Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Blank line in string value in insert statement causing problems

652689Jan 7 2009 — edited Jul 29 2011
I am generating an sql statement (insert) from an xml document using xsl transformation that is later run in an sqlplus session. The issue I am coming across is that one of the tags contains free-form text that can be any number of lines. Most strings are not causing a problem, but when there is a blank line in the string (two or more endline characters together) sqlplus seems to think this signifies the end of a statement. Here is an example that I hope will clarify what is being created.

INSERT INTO MYTABLE VALUES('THIS WORKS', 0, NULL, ...);

INSERT INTO MYTABLE VALUES('THIS
ALSO
WORKS', 0, NULL, ...);

INSERT INTO MYTABLE VALUES('THIS

DOES NOT
WORK', 0, NULL, ...);

Anyone seen this problem before or have a quick fix? All I can come up with is to pre-process the xml document (which, by the way, I have no control over the format as it comes from a client) to compress vertical whitespace. (This seems like overkill to me). Any suggestions would be appreciated.
Thanks,
Daniel
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 26 2011
Added on Jan 7 2009
9 comments
2,177 views