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

Siotos Evaggelos <sevag_at_eurodyn.com> wrote in message news:<3E35544A.C7C21A52_at_eurodyn.com>...
> 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.
> -->
>
> 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

The note you are referring to, I am sure, deals with pre 9i releases. Functionality to read/write RAW data using utl_file was added in 9i. Specifically, functions get_raw and put_raw were added. My guess is that these two functions are probably sitting on top of java.io api.

However, since you brought it up... I should have said that the first method is for 9i and above *ONLY* and the second method (Using java) is for 8i and above OR for pre 8i...if jdbc driver supports reading BLOBS. Regards
/Rauf Sarwar Received on Mon Jan 27 2003 - 15:39:33 CST