Re: Store large Word or PDF file in DB

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;

      ---

Stefano Biotto <sbiotto_at_tiscali.it> wrote in message news:<a2hv4p$j1e$1_at_pegasus.tiscalinet.it>...

<snip>

>
> This is for loading a binary file in the db,
> anyone knows how download the file stored in a lob
> in a new a file of the o.s.?
Received on Tue Jan 22 2002 - 03:07:26 CST