SQLPLUS multi-line quoted text

This document demonstrates some of the problems I've encountered when using SQLPlus to insert text that has multiple lines. You can run the INSERT statements below, to demonstrate the specific problem which I state in the actual text being inserted.

--Note that these problems occur only with SQLPlus, Using TOAD, (and I assume SQL Developer), there are no errors with any of the inserts.
I used SQLPLus 11g client on Windows 7. Different configurations may produce different results.

I'd be interested in hearing about any other anomalies you find with multi-line text.


CREATE TABLE TXT (str varchar2(500));


set echo on

set sqlblanklines off

set define off

spool multiline_text.log

-------- In these examples I am using Oracle 'Q' strings: aka Quoted Strings

-------- For example, instead of quoting a string like this:

--------   'Joe''s Garage is where the ''action'' is'

-------- I do it like this:

--------   q'~Joe's Garage is where the 'action' is~'

-------- In a quoted string, the character after the initial q'

-------- can be any character that is not in the actual string content.

-------- I've chosen to use the tilde character.

---------- THE BLANK LINE --------

INSERT INTO txt ( str) 

VALUES (q'~This shows it is NOT okay to have a 

blank line unless you 'set sqlblanklines on'.~');

set sqlblanklines on

INSERT INTO txt ( str) 

VALUES (q'~This shows it is okay to have a 

blank line after you 'set sqlblanklines on'.~');

---------- THE SLASH CHARACTER --------

INSERT INTO txt ( str) 

VALUES (q'~This shows it is NOT okay to have a 

/

slash by itself on a line~');

INSERT INTO txt ( str) 

VALUES (q'~This shows it is [NOT] okay to have a 

/ 

slash followed by a space character. You can't see it, but it's there.~');

INSERT INTO txt ( str) 

VALUES (q'~This shows it is okay to have a 

/slash as a starting character provided that the

slash is [not the only] character~');

INSERT INTO txt ( str) 

VALUES (q'~This shows it is okay to have a 

/ slash as a starting character

even if it is followed by a blank.~');

-----------THE DOT CHARACTER----------

INSERT INTO txt ( str) 

VALUES (q'~This shows it is [not] okay to have a 

.

dot on a blank line by itself.~');

INSERT INTO txt ( str) 

VALUES (q'~This shows it is [not] okay to have a 

.  

dot plus some trailing blanks on a line by itself.~');

INSERT INTO txt ( str) 

VALUES (q'~This shows it is okay to have a 

. dot plus more text

even when dot is the first character~');

---------THE SEMI-COLON CHARACTER ------

INSERT INTO txt ( str) 

VALUES (q'~This shows it is okay to have a; 

blank line provided you set sqlblanklines on~');

INSERT INTO txt ( str) 

VALUES (q'~This shows it is okay to have a; plus more

blank line provided you set sqlblanklines on~');

---------THE POUND SIGN CHARACTER ------

INSERT INTO txt ( str) 

VALUES (q'~This shows it [IS] okay to have a 

#

pound-sign on a line by itself~');

INSERT INTO txt ( str) 

VALUES (q'~This shows it is [not] okay to have a 

# plus more

text when the pound sign is the first character~');

INSERT INTO txt ( str) 

VALUES (q'~This shows it is  okay to have a 

a pound-sign # so long

as the #is not the first character~');

-- ;

rollback;

spool off

--------------------------------RESULTS----------------------

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\mmoore>sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 16 11:16:11 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: / @mydb

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> set sqlblanklines off
SQL> set define off
SQL> spool multiline_text.log
SQL>
SQL> -------- In these examples I am using Oracle 'Q' strings: aka Quoted Strings
SQL> -------- For example, instead of quoting a string like this:
SQL> --------   'Joe''s Garage is where the ''action'' is'
SQL> -------- I do it like this:
SQL> --------   q'~Joe's Garage is where the 'action' is~'
SQL> -------- In a quoted string, the character after the initial q'
SQL> -------- can be any character that is not in the actual string content.
SQL> -------- I've chosen to use the tilde character.
SQL>
SQL> ---------- THE BLANK LINE --------
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is NOT okay to have a
  3
SQL> blank line unless you 'set sqlblanklines on'.~');
SP2-0734: unknown command beginning "blank line..." - rest of line ignored.
SQL>
SQL> set sqlblanklines on
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a
  3
  4  blank line after you 'set sqlblanklines on'.~');

1 row created.

SQL>
SQL>
SQL> ---------- THE SLASH CHARACTER --------
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is NOT okay to have a
  3  /
ERROR:
ORA-01756: quoted string not properly terminated


SQL> slash by itself on a line~');
SP2-0734: unknown command beginning "slash by i..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is [NOT] okay to have a
  3  /
ERROR:
ORA-01756: quoted string not properly terminated


SQL> slash followed by a space character. You can't see it, but it's there.~');
SP2-0734: unknown command beginning "slash foll..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a
  3  /slash as a starting character provided that the
  4  slash is [not the only] character~');

1 row created.

SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a
  3  / slash as a starting character
  4  even if it is followed by a blank.~');

1 row created.

SQL>
SQL>
SQL>
SQL> -----------THE DOT CHARACTER----------
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is [not] okay to have a
  3  .
SQL> dot on a blank line by itself.~');
SP2-0734: unknown command beginning "dot on a b..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is [not] okay to have a
  3  .
SQL> dot plus some trailing blanks on a line by itself.~');
SP2-0734: unknown command beginning "dot plus s..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a
  3  . dot plus more text
  4  even when dot is the first character~');

1 row created.

SQL>
SQL>
SQL> ---------THE SEMI-COLON CHARACTER ------
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a;
ERROR:
ORA-01756: quoted string not properly terminated


SQL>
SQL> blank line provided you set sqlblanklines on~');
SP2-0734: unknown command beginning "blank line..." - rest of line ignored.
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a; plus more
  3
  4  blank line provided you set sqlblanklines on~');

1 row created.

SQL>
SQL>
SQL> ---------THE POUND SIGN CHARACTER ------
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it [IS] okay to have a
  3  #
  3  pound-sign on a line by itself~');

1 row created.

SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is [not] okay to have a
  3  # plus more
SP2-0042: unknown command "plus more" - rest of line ignored.
  3  text when the pound sign is the first character~');

1 row created.

SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is  okay to have a
  3  a pound-sign # so long
  4  as the #is not the first character~');

1 row created.

SQL>
SQL> -- ;
SQL>
SQL> rollback;

Rollback complete.

SQL> spool off