Toad World® Forums

Dataset export as insert statements also for CLOB column


#1

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(’…’)
);


#2

There is a dropdown labelled “columns to exclude”. Click it and uncheck CLOB.


#3

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.


#4

I see. There is no way to do that as of now but I can put it on my to-do list.


#5

:ok_hand: That would be great! Thanks!


#6

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.


#7

Hi John,
just a short feedback. I’ve just downloaded the current beta and it works perfect!
Thank you!


#8

Thanks for the feedback! I’m glad to hear it.


#9

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?


#10

Yes, I’ll drop it down to 2000.