Was there a change form 12.11 to 12.12 in the formatting of the exported data when exporting as Merge or insert statements? I recently upgraded from 12.11 and noticed that my large VARCHAR2 fields if they contain CR and/or LF, the exported data now concatenates CHR(13) and CHR(10), instead of keeping the data intact as a full string. This is a concern because I generate backup scripts week to week and now they are different enough to make it difficult to determine if any changes occurred over the past week.
Is there a setting where I can return the output to the original formatting?
The change was made because if the data contained multiple line feeds (In other words, data followed by 2 line feeds, followed by more data), and you attempt to run the insert statement in SQLPlus, it would fail - because SQLPlus mistook the 2nd line feed as an “end of statement” and attempted to run an incomplete Insert statement.
There is no option to return to prior formatting, sorry. But I think this is better, going forward.
I have definitely suffered from the inability of SQL*Plus to actually recognise an end of statement by the end of statement character, the semicolon!
However, what I do if I want blank lines, is to add a couple of hyphens on the blank lines, thus embedding a comment, and preventing SQL*Plus from losing the plot.
Perhaps something like that could be done here to allow “blank lines” as empty comment lines?
I started to make this so that it left single line feeds alone and only replaced multiple line feeds…which wouldn’t be so bad if I only had to consider single or double line feeds, but the code was starting to get ugly when I had to count all of the line feeds, keeping in mind that they could be CHR(10) or CHR(13)||CHR(10), or possibly (however unlikely) a mixture of the two. So I just decided to replace them all. But I can revisit this and only replace multiple line feeds if there is a lot of pushback on the way it is now.
As an aside, I encourage you to participate in the beta program, so that we can get more feedback like this during the beta cycle rather than just after a release.
Thank you John, I wasn’t sure if it was a setting that didn’t get propagated with the new version. I am fine with the new format, I manually compared the two versions verified they were ultimately the same, so going forward I will be fine.
Thanks again for you quick response, and thanks to the others for their suggests.
Guys, sqlplus can handle double newlines if you use
SET SQLBLANKLINES ON
Even semicolons in the data can be worked around by using
SET SQLTERMINATOR OFF
The real problem with sqlplus that actually has no solution is when your data has slashes by themselves. Try this for fun:
INSERT INTO mytable VALUES ('This semicolon won't work
xyzpqr;zzzzzzz
/
');
INSERT INTO mytable VALUES ('This slash is ok /
and this / too
but this one is not
/
');
sqlplus is seriously too primitive and limiting as a deployment tool at this point and badly needs phasing out. I wish everyone would start adopting SQLcl soon.