Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
299 views
Welcome To Ask or Share your Answers For Others

1 Answer

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...