How to save a BLOB to a file on disk in PL/SQL
zorro
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
Rauf Sarwar
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.
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
Siotos Evaggelos
Rauf Sarwar wrote:
>
> "zorro" <z...@z.com> wrote in message news:<BZ6Z9.129790$H7.52...@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
zorro
"Rauf Sarwar" <rs_a...@hotmail.com> wrote in message
news:92eeeff0.03012...@posting.google.com...
Rauf Sarwar
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
Rauf Sarwar
> I want to do it in PL/SQL, not in Java, Thanks.
I did not realize this before.... please DO NOT CROSSPOST to every
Oracle group that you can spell.
If you are on 9i then you can use plsql as described in step 1.
> > 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.
If you are on pre 9i then you have no choice but to use java as
described in step 2 or upgrade to 9i.
SoulSurvivor
<BIG SNIP>
> If you are on pre 9i then you have no choice but to use java as
> described in step 2 or upgrade to 9i.
>
Disagree strongly.
In Oracle 8i, i sucessfuly did what the original poster is trying to
do by using an External Procedure, written in C and 'downloaded' a
BLOB to disk in PL/SQL.
Docoments on how to do this are on Orcle Metalink website and a year
or two ago, i posted a message on this newsgrop on how to achieve it.
One link being
http://groups.google.com/groupsq=markg+blob&hl=en&selm=ab87195e.0201220107.6cdeeaeb%40posting.google.com&rnum=2
I've cut and pasted the text from back then, see below. You'll have
to do your own Metalink search for the External procedure stuff.
Mark
=============================
Yes but its not so straightforward.
Use DBMS_LOB to read from the BLOB
You will need to create an external procedure to take binary data and
write it to the operating system, the external procedure can be
written in C. If it was CLOB data, you can use UTL_FILE to write it
to the OS but UTL_FILE does not support the binary in a BLOB.
There are articles on MetaLink explaining how to do and it has a C
program ready for compiling and the External Procedure stuff, i'd
advise a visit.
Especially, look for Note:70110.1, Subject: WRITING BLOB/CLOB/BFILE
CONTENTS TO A FILE USING EXTERNAL PROCEDURES
Here is the Oracle code cut and pasted from it. The outputstring
procedure is the oracle procedure interface to the External procedure.
I tried it about a year ago and worked fine.
The above is a starter, hope it helps! ;-)
M
-------------------------------------
declare
i1 blob;
len number;
my_vr raw(10000);
i2 number;
i3 number := 10000;
begin
-- get the blob locator
SELECT c2 INTO i1 FROM lob_tab WHERE c1 = 2;
-- find the length of the blob column
len := DBMS_LOB.GETLENGTH(i1);
dbms_output.put_line('Length of the Column : ' || to_char(len));
-- Read 10000 bytes at a time
i2 := 1;
if len < 10000 then
-- If the col length is < 10000
DBMS_LOB.READ(i1,len,i2,my_vr);
outputstring('p:\bfiles\ravi.bmp',rawtohex(my_vr),'wb',2*len);
-- You have to convert the data to rawtohex format. Directly sending
the buffer
-- data will not work
-- That is the reason why we are sending the length as the double the
size of the data read
dbms_output.put_line('Read ' || to_char(len) || 'Bytes');
else
-- If the col length is > 10000
DBMS_LOB.READ(i1,i3,i2,my_vr);
outputstring('p:\bfiles\ravi.bmp',rawtohex(my_vr),'wb',2*i3);
dbms_output.put_line('Read ' || to_char(i3) || ' Bytes ');
end if;
i2 := i2 + 10000;
while (i2 < len ) loop
-- loop till entire data is fetched
DBMS_LOB.READ(i1,i3,i2,my_vr);
dbms_output.put_line('Read ' || to_char(i3+i2-1) || ' Bytes ');
outputstring('p:\bfiles\ravi.bmp',rawtohex(my_vr),'ab',2*i3);
i2 := i2 + 10000 ;
end loop;
end;
=============================