Re: Extracting Files Stored as BLOBs

I have answered this type of question in the past so it may be worth going a search on Google (groups.google.com) for responses.

Worth doing a search on "dbms_lob external".

I've cut and pasted a solution below. There are probably similar ones involving Java.

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;

      ---

"Gnarly" <nick.marks_at_uk.thalesNOSPAMgroup.com> wrote in message news:<b8rbqv$qrv$1_at_rdel.co.uk>...
> I am working on migrating data from a legacy application that stores files
> as BLOBs in an oracle table, I need to get these files out to the OS for
> input to the new system.
>
> Is there a Tool for extracting these files out to OS level files?
Received on Fri May 02 2003 - 02:49:29 CDT