Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Inserting multi-line strings with SQLcl & SQLBLANKLINES

2 Mins Read

Question from a friend:

I have a string with embedded newlines and empty lines – when i set SQLFORMAT to INSERT, the resulting SQL is invalid as SQLcl doesn’t accept blank lines in a literal

Simon

Simon says he wants to do an INSERT with embedded multi-line strings, and some of those lines include empty lines. So let’s see how that could work.

SET sqlbl ON
CREATE TABLE simon (
    ID INTEGER,
 WORDS varchar2(100);
INSERT INTO simon VALUES (
 1,
 'This is a multi-line
string.
 
 
Ok?
');
 
COMMIT;

And running this block of code in SQlcl, we get a row inserted, committed. And if I query it back out in a tool that shows formatted text, then I can see it went in as expected.

Data inserted as expected, sweet.

This isn’t a SQLcl command.

SET SQLBLANKLINES goes back at least as far as 9i. It’s documented as –

Controls whether SQL*Plus allows blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.

Doing the same type of INSERT in SQL*Plus, I can see –

Same behavior, just also a less-friendly editor/UX.

Takeaways

If you’re using INSERT scripts, make sure you have the environment set based on your needs. For example if you have %’s in your data, consider SET SCAN | DEFINE OFF

And if you have multi-line strings with blank lines, consider SET SQLBL ON.

If you don’t have this taken care of, your CLI may attempt to run the first block of code until it finds a blank line and give you an error like below or just exit you out of the EDIT mode and back to your prompt without having sent any code to the database.

SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 – “quoted string not properly terminated”

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

3 Comments

  1. jay Reply

    That’s correct. Due to our in-house policies we are not permitted to use database links. Therefore, we decided to use BRIDGE command instead.

  2. jay Reply

    Hello Jeff,

    WHENEVER SQLERROR isn’t a SQLcl command, either.
    I’m using a BRIDGE command. If it has any issues, for instance a target database down or table does not exist, WHENEVER SQLERROR is helpless.
    Is there any possibility to implement some kind of WHENEVER BRIDGEERROR in sqlcl? Or would it be any workaround for this problem?

    Thanks,
    jay

    • thatjeffsmith Post Author

      So you’re automating data moves and you want your script to return the proper exit code if a BRIDGE command fails, more or less?

Write A Comment