8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Export CLOB

Scott Wesley said...

G'day Tim,

I don't know if this is environment/context related, but I needed to use utl_file.put_LINE in order to for the file to be complete, not just the first buffer in size.


Gerit Wissing said...

Hi Scott,
I had the same issue. Consider the extra line break at the end of the 32K buffer with utl_file.put_LINE. In my case UTL_FILE.PUT & UTL_FILE.FFLUSH helped.

Raghavendra Anupoju said...

Good Example.

But I had a problem. My CLOB object is having 1932 lines and example is returning only 832 lines.

How to export whole file? i.e length more than 32767.

Tim... said...


I see no reason for it to do that. It should work for any size of CLOB, even those above 32K. The 32767 limit is the maximum line size that UTL_FILE can handle, not the maximum size of the document in total.

I'm guessing you are seeing some error other than NO_DATA_FOUND. I've amended the example so that it traps the error and re-raises other errors.



Raghavendra Anupoju said...

Thanks for reply Tim.

When I'm executing the block with 32767, the output file size is 32768 kb. Actual size of the file is 68,233 kb. Executed the update example and Procedure successfully completed without errors.

Please suggest me to get the whole file from db to file.

Thanks in adv

Tim... said...


This is the sort of behaviour I would expect If you are using a multibyte character set. Although you think you have 32767 characters, this is actually represented by more than one byte per character, hence the larger file size.

No more questions in the comments please. If you have questions they should be asked in the forum. There is massive red text telling you that. :)



hervé said...

golden code, congratulations !

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.