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?

Jon Heller · Accepted Answer · 2016-02-26 17:38:12Z

65

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, 2016 at 17:38

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

answered Feb 1, 2012 at 3:31

jim mcnamara

16.5k2 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sorry-Im-a-N00b Over a year ago

Will SQLBLANKLINES remain "on" for the next query, or does it only effect the current query?

jim mcnamara Over a year ago

It affects the entire session - from connect to logout. So you may have to set it OFF or ON as needed.

Jérôme MEVEL · Accepted Answer · 2019-11-05 09:12:15Z

27

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 5, 2019 at 9:12

Jérôme MEVEL

7,9738 gold badges54 silver badges84 bronze badges

answered Feb 26, 2016 at 15:45

ibre5041

5,3261 gold badge24 silver badges40 bronze badges

1 Comment

jpmc26 Over a year ago

This is better since it's not SQL*Plus specific. +1.

A.B.Cade · Accepted Answer · 2012-02-01 06:28:15Z

4

Another way of inserting newlines to a string is concatenating:

chr(13)||chr(10)

(on windows)

or just:

chr(10)

(otherwise)

answered Feb 1, 2012 at 6:28

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Comments

Chad · Accepted Answer · 2017-04-02 02:54:34Z

4

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.

edited Apr 2, 2017 at 2:54

answered Feb 26, 2016 at 14:55

Chad

3,0363 gold badges29 silver badges41 bronze badges

2 Comments

FORTRAN Over a year ago

So how do you insert a multiline string with empty lines and lines starting with # ?

FORTRAN Over a year ago

Just solved it, I use set sqlprefix * before the query and then set it back to normal afterwards set sqlprefix #

Collectives™ on Stack Overflow

insert a multiline string in Oracle with sqlplus

4 Answers 4

2 Comments

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related