This content has been marked as final. Show 7 replies
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
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