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

rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0301271539.359fb042_at_posting.google.com>...
> "zorro" <z_at_z.com> wrote in message news:<y0dZ9.130423$sV3.4961522_at_news3.calgary.shaw.ca>...

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

      ---