insert a multiline string in Oracle with sqlplus

Question

I have a SQL script that will insert a long string into a table. The string contains a new line (and this new line is absolutely necessary), so when it is written in a text file, the query is split to multiple lines. Something like:

insert into table(id, string) values (1, 'Line1goesHere 

Line2GoesHere 
blablablabla
');

This runs ok in Toad, but when I save this as a .sql file and run it using sqlplus, it considers each line a separate query, meaning that each line will fail (beacuse insert into table(id, string) values (1, 'Line1goesHere, Line2GoesHere aren't well-formated scripts.

SP2-0734: unknown command beginning "Line2GoesHere" - rest of line ignored.

Is there a way to fix this?

score 62 · Accepted Answer · edited Feb 26 '16 at 17:38

62

Enable SQLBLANKLINES to allow blank lines in SQL statements. For example:

SET SQLBLANKLINES ON
insert into table(id, string) values (1, 'Line1goesHere 
Line2GoesHere 

blablablabla
');

The premise of this question is slightly wrong. SQL*Plus does allow multi-line strings by default. It is only blank lines that cause problems.

edited Feb 26 '16 at 17:38

Jon Heller

34,999
6
74
132

answered Feb 01 '12 at 03:31

jim mcnamara

16,005
2
34
51

Will SQLBLANKLINES remain "on" for the next query, or does it only effect the current query? – Sorry-Im-a-N00b Apr 14 '14 at 15:49
2

It affects the entire session - from connect to logout. So you may have to set it OFF or ON as needed. – jim mcnamara Apr 21 '14 at 21:10

score 23 · Answer 2 · edited Nov 05 '19 at 09:12

23

You can also use not-well-known feature of Oracle's SQL: Perl style quoted strings.

SQL> select q'[f dfgdfklgdfkjgd
  2  sdffdslkdflkgj dglk
  3  glfdglkjdgkldj ]'
  4  from dual;

Q'[FDFGDFKLGDFKJGDSDFFDSLKDFLKGJDGLKGLFDGLKJDGKLDJ]'
----------------------------------------------------
f dfgdfklgdfkjgd
sdffdslkdflkgj dglk
glfdglkjdgkldj

edited Nov 05 '19 at 09:12

Jérôme MEVEL

7,031
6
46
78

answered Feb 26 '16 at 15:45

ibre5041

4,903
1
20
35

2

This is better since it's not SQL*Plus specific. +1. – jpmc26 Jul 19 '17 at 12:17

Chad · Answer 3 · 2017-04-02T02:54:34.453

SQL*Plus Manual

You can end a SQL command in one of three ways:

with a semicolon (;)

with a slash (/) on a line by itself

with a blank line

A blank line in a SQL statement or script tells SQL*Plus that you have finished entering the command, but do not want to run it yet. Press Return at the end of the last line of the command.

Turning SQLBLANKLINES on in this situation may be the answer, but even with it you still have to worry about the following SQL*Plus commands.

@  ("at" sign)        (Start of line) 
@@ (double "at" sign) (Start of line) 
#   SQLPREFIX         (Start of line)
.   BLOCKTERMINATOR   (Start of line and by itself)
/  (slash)            (Start of line and by itself)
;   SQLT[ERMINATOR]   (Start of line and by itself, or at the end)

SQLPREFIX is something that you cannot turn off; it's a feature of SQL*Plus. BLOCKTERMINATOR can be activated or disabled. Slash on the other hand if it appears at the start of a new line will cause it to execute the contents in the buffer. SQL[TERMINATOR] has a similar behavior.

So how do you insert a multiline string with empty lines and lines starting with # ? — FORTRAN, Mar 29 '18 at 06:47
Just solved it, I use set sqlprefix * before the query and then set it back to normal afterwards set sqlprefix # — FORTRAN, Mar 29 '18 at 07:01

score 3 · Answer 4 · answered Feb 01 '12 at 06:28

3

Another way of inserting newlines to a string is concatenating:

chr(13)||chr(10)

(on windows)

or just:

chr(10)

(otherwise)

answered Feb 01 '12 at 06:28

A.B.Cade

16,735
1
37
53

insert a multiline string in Oracle with sqlplus

4 Answers4

Linked