Toad World® Forums

Dataset export as insert statements also for CLOB column


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.


:ok_hand: 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.


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.