From: "Gnarly" Newsgroups: comp.databases.oracle.tools,comp.databases.oracle,comp.databases.oracle.misc Subject: Re: Extracting Files Stored as BLOBs Date: Tue, 6 May 2003 15:18:16 +0100 Organization: Thales Lines: 91 Message-ID: References: <8d9c6fd.0305012349.4cb4b92c@posting.google.com> NNTP-Posting-Host: ntwc0216.int.rdel.co.uk X-Trace: rdel.co.uk 1052230699 11398 172.21.187.118 (6 May 2003 14:18:19 GMT) X-Complaints-To: postmaster@uk.thalesgroup.com NNTP-Posting-Date: 6 May 2003 14:18:19 GMT X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Many thanks I'll try that. "SoulSurvivor" wrote in message news:8d9c6fd.0305012349.4cb4b92c@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" wrote in message news:... > > 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?