Would it be possible to get a dataset export as insert statements also for CLOB columns?
Oracle’s SQL Developer is able to create insert statements like the following from records with CLOB columns:
Insert into (PK, TEXT, CLOB-TEXT)
VALUES (1,‘short text’, TO_CLOB(‘very long CLOB test - column char1 to 500’)
|| TO_CLOB(‘very long CLOB text - column char 501 to 1000’)
|| TO_CLOB(’…’)
);
There is a dropdown labelled “columns to exclude”. Click it and uncheck CLOB.
Yes it’s possible to unceck this option and I’ll get an INSERT with a realy long text block (~48000 chars), but unfortunately this INSERT is not executable as L get an exception “ORA-1704 string literal too long”. It has to be an export with the TO_CLOB() function to get an runing statement.
I see. There is no way to do that as of now but I can put it on my to-do list.
That would be great! Thanks!
OK, this is done for next beta. You’ll still have to uncheck that box, but long strings will be concatenated to prevent the error.
1 Like
Hi John,
just a short feedback. I’ve just downloaded the current beta and it works perfect!
Thank you!
Thanks for the feedback! I’m glad to hear it.
Hi John,
We tried today to run the generated scripts with to_clob function on a customer database using sqlplus. Unfortunately we got an error
SP2-0027: Input is too long (> 2499 characters) - line ignored.
This is a sqlplus fixed line length limitation.
So I would like to ask, if you can enter a line break every 1000 - 2000 characters for the CLOB columns?
Yes, I’ll drop it down to 2000.
1 Like
Follow up on this one:
a. is it available in latest (none beta) version? 13.3 is creating lines longer than 2000
b. is it possible to choose line terminator? If "insert" is intended for unix, there is no need for chr(13)
I've just checked it in 14.0.75 (latest none beta)
a) The generated lines contain value strings with a length of 2000 chars, the line total is something round 2017 chars.
b) Yes you can select UNIX style file generation ...
1 Like
Thanks a lot.
You mentioned earlier that SQLDeveloper can do that. However, my 20.2 (Mac) seems to skip CLOB all together. How do you get SQLDev to do that?
I get a line of 3552 bytes and CHR(13)||CHR(10) with Unix Style Save set.
Using a freshly downloaded 14.0.75
What am I doing wrong?
It doesn't make only CHR(13) if you check the Unix Style Save box. Juergen was mistaken when he said that. However, Toad will only give CHR(13) || CHR(10) if that is what is in the database. If you have only CHR(13) then Toad will give that in the output.
Also, the total line length may exceed 2000 characters. 2000 is the limit for literals in the insert statement, not for the SQL line length. A lot of line feeds within your CLOB text turning into CHR(13) || CHR(10) could increase the length of the line of SQL.
@Abraham_Olsen @juergen.rausch
Did I misunderstand the problem? Does the SQL line length need to stay under 2000? I thought it was just the length of the quoted text within the lines that need to be limited.
We use Toad to generate SQL INSERT statements to run with SQLPlus. SQLPlus has a line length limit of 2499 CHAR in total (incl. the TO_CLOB(...) etc.). So I've asked John to split the content of a field into 2000 char blocks that will be put into several TO_CLOB () functions and concated by || in new lines. The line break between two of this lines is what I thought you will switch from 13/10 to only 10 (using the UNIX Style option). There is no change made in the content of field data. If you have a 13/10 in the field, it still remains in the generated statement.
Hope this clarifies what will be done here.
Thanks for the replies.
I have just tested an insert statement created using Toad 14.0.75.
The CLOB has a length of 12137 bytes.
If I strip the lines created by Toad so I'm left with the actual string to be inserted, they are all at max 2000 - more precisely, as expected, 6 lines of 2000 and a seventh line with 137.
So this part seems to be accurate.
But when I run the script from sqlplus, I receive this error:
SP2-0341:
line overflow during variable substitution (>3000 characters at line 7)
sqlplus has a documented command-line length 2500 characters.
Example: SQL*Plus Limits
I believe that the error I receive has to do with a command, that is longer than 3000.
Ok, I'll make a change for 14.2 so that the line length, including CHR(13) and CHR(10), stays under 2000. That will keep us under the limits and also leave a little breathing room.
1 Like
Just to understand what happens at your side.
You have a CLOB that is splitted in 6 lines of TO_CLOB (<2000 CHARS>) ||
and one line wit TO_CLOB(<137 CHARS>)
So where do you get a line that is longer than 2500 Chars?
I've done this with much longer CLOBs (something about 20 lines of TO_CLOB()) and never get this error during execute!?!
The length of the command in SQLplus can be much longer that the 2500 chars. This is only a line length limit.
Maybe you should try another option for script generating "one column value per line" (see screenshot)
hope this helps...