Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Export CLOB - A simple method for exporting the contents of a CLOB datatype to the filesystem.



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.

Scott

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.
regards
Gerit

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

Hi.

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.

Cheers

Tim...

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
Raghavendra

Tim... said...

Hi.

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

Cheers

Tim...

DO NOT ask technical questions here, that's what my forum is for!

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!

Add your comments here.
Name
Comment
(max 400 chars - plain text)