Hi Juergen,1 person found this helpful
Here are a couple of threads from this forum (over 4 years apart) discussing CLOB truncation, for both Run Script and Run Statement .
1. EA1 - Clob truncated ?
2. Is there a 4000 char limit for an xlsx cell during export?
I'm not sure if anyone ever logged a preference enhancement in SQL Exchange as Barry suggested in (1), or how the behavior has been improved in some future release as Jeff Smith implies in (2).
In any event, I suspect this will become less of an issue after a future Oracle release supports a VARCHAR2 limit of 32000 or 32K. Until something like that happens, I suspect your best bet would be to search for a way of splitting the CLOB into multiple output lines, perhaps in the SQL and PL/SQL forum, like someone asked here:
split clob data
SQL Developer Team
FYI: http://www.java2s.com/Tutorial/Oracle/0601__System-Packages/Printclobdataout.htm1 person found this helpful
Thanks a lot Gary for your help.
I have tried all the export settings in Preferences, no change. The problem is that a clob doesn't even print out 4K but only 3 lines, just try:
select filename, my_clob from table where id = 1;
I can not use PL/SQL either to split the clob, because I want to use the spool command to write it to a file.
I have a table with filename and clob content. All I want is to get the clob content out of the table into a file named with the stored corresponding filename. And this should somehow work as a script that I don't have to do it manually for hundreds of records.
Maybe you have a different idea to accomplish that? I can not use a file writing routine to write the files to the DB-Server, I will never get access to the server. It has to be a client side solution.
Sorry for the late reply. I just posted that link without checking how it would work. Actually you may find the following acceptable:
Then, in your worksheet, try something like this:
--Note this is a FUNCTION now, not a PROCEDURE. create or replace function printClob( p_clob in clob ) return varchar2 as l_offset number default 1; begin loop exit when l_offset > dbms_lob.getlength(p_clob); dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) ); l_offset := l_offset + 255; end loop; return null; end;
Not perfect, but good for the client-side.
set serveroutput on 100000 spool mycloboutput.log select filename, printclob(my_clob) from table where id = 1 spool off
Yeah! Thats what I need, I didn't realize that I can spool dbms_output.put_line()
Great Job, Thanks a lot Gary!
But if I need to perform such task for around 10 Lakh records, is there any alternative or any other way to display CLOB records of more than 5000 bytes and be able to export them? (with reference to huge database).
Thanks in advance.