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 literalSimon
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.
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 –
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”