write clob data to a file

Unfortunately, the only way in pl/sql is to use utl_file.

If you had some java or an external routine written in c, your pl/sql procedure could use them to write out the clob to files but in pl/sql  utl_file is it.

Hi,

If I write a Java code to write the clob data to a file, which will be the fastest among utl_file and java code?

why are you writing the CLOB to a file?

Hi,

Actually my Business Requirement is to write data from a table to a file.
So, Instead of fetching each and individual column, I am feching all the column names from all_tab_columns and appending them (ex: id,number column are there.. I am appending in select statement as id|','||number)andwritting the clob to a file.

so, the "business person" told you to write the data to a file..  
they must be FAR smarter than any business person I've ever worked with.

what are they going to do with the file?  my guess is one of these:
    1)  load it into excel
    2)  load it into another database

there is almost NEVER a reason to write data to a file

you'll have to do your own benchmarking for the throughput on your system to determine which will be faster.  They "should" be roughly equivalent in writing speed.

However, in java you will be able to work with larger strings, since pl/sql can only work with 32K chunks, whereas a java String type can hold your entire clob.  So there should be fewer io operations, but, pl/sql may give you better db interaction so it may be able to retrieve and manipulate the data faster than java even though it may take more steps to write it out.


As to dbmullen's question,  what is your Business Unit going to do with the file once you've created it?  Will it simply be loaded into another database?  If so,  don't write the file.   Most databases can talk directly to one another through various gateways and database links.

Will the file be loaded into MS Excel? If so, dont't write the file, just establish an odbc link and let Excel import the data directly.

Will the file be sent via ftp to some other server?  You can do that directly from the database too.

Does the file have any value itself?  Or is it just a means to some other end.  If it's just a middleman, look at the problem a little more and see if it's even necessary.

Hi..

Thanks for ur comments..Actually , my requirement is to archive data and place the data on my server in the form of either '.txt' or '.csv' files and remove the data form the table.When the user wants to reload and check the data , we need to read the data from the files and place the data into the tables.

At present,i am writing the data into the text file using PL/SQL by chunking the data into 32 KB.

T hanks for the solution