sql - 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?

question from:https://stackoverflow.com/questions/9090072/insert-a-multiline-string-in-oracle-with-sqlplus

Answer 1

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.