Re: How to save a BLOB to a file on disk in PL/SQL

In article <3E35544A.C7C21A52_at_eurodyn.com>, Siotos says...
>
>
>
>Rauf Sarwar wrote:
>>
>>"zorro" <z_at_z.com> wrote in message
>>news:<BZ6Z9.129790$H7.5248099_at_news2.calgary.shaw.ca>...
>> > Hi,
>> >
>> > I know how to load a binary file into a BLOB column, but I'd like to do the
>> > opposite now and create a file on disk with the data from a BLOB column.
>> >
>> > Anybody can help ?
>> >
>> > Thank you,
>> > Denis
>>
>> 1) Use dbms_lob and utl_file packages. You should be able to open an
>> io stream by getting RAW data from BLOB column by using dbms_lob.read
>> and output to a file using utl_file.Put_Raw, theorectically because I
>> have not tested it myself.
>>
>
>This approach won't work because the utl_file packages work only with
>TEXT data.
>
>From MetaLink:
>-->
>--> Note: The utl_file package is only suitable for handling TEXT data.
>-->
>

utl_file.put_raw is new 9iR2 functionality that works with RAW (binary) data so in fact it would be a way to do it.

>Doc ID: Note:61737.1
>
>
>
>> 2) Use java.sql and java.io api's and write a java stored procedure.
>> There are functions in java.sql to get to BLOB data. Look at
>> java.sql.Blob and java.sql.ResultSet.getBlob(). Once you get the
>> handle to InputStream from java.sql.Blob, you can use
>> java.io.FileOutputStream and write data to file.
>>
>> Regards
>> /Rauf Sarwar
>
>I have never tried this approach. :) Zorro, if you find a way to do that
>just reply this thread.
>
>
>Regards
>Vangelis

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp