Blank line in string value in insert statement causing problems
652689Jan 7 2009 — edited Jul 29 2011I 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