Many thanks I'll try that.
"SoulSurvivor" <markyg_7_at_yahoo.co.uk> wrote in message
news:8d9c6fd.0305012349.4cb4b92c_at_posting.google.com...
> 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 Tue May 06 2003 - 09:18:16 CDT
