Store large Word or PDF file in DB

[cschang]

Is it possible to storage a very larg word file ( more than 4K
charcaters) or even PDF type file into database? So I can use some kind
of ID column to be referenced by other . I have a small set of records
which each row is associated with a larger text file and gif file. So
I try to put the large word ( or PDF that will include some picture in
it) into another table but use a foreign ID to reference them. Thanks.

C CHang

[Niall Litchfield]

look into the BLOB datatype (8i and above). You can either store the data
directly or as a BFILE (external file reference).

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
"cschang" <csc...@maxinter.net> wrote in message
news:3C42660...@maxinter.net...

[Stefano Biotto]

Niall Litchfield wrote:

You have to create a table with a blob column:
create table t_blob(id number not null primary key,
blob_file blob);
Create a link to the directory where the file to load in the blob is
(connect as system then grant read privilege to your user):
create or replace directory blob_dir as 'C:\Documents';

Custom the following procedure that I copied from a Thread

create or replace procedure blob_ins(p_id in number, p_filename in
varchar2) as
l_bfile bfile;
l_blob blob;
begin
insert into blob_test(p_id, empty_blob())
returning blob_file into l_blob;

l_bfile := bfilename('BLOB_DIR', p_filename);
dbms_lob.fileopen(l_bfile);
dbms_lob.loadfromfile(l_blob, l_bfile, dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);
commit;
return;
end blob_ins;

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.?

[MarkyG]

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 <sbi...@tiscali.it> wrote in message news:<a2hv4p$j1e$1...@pegasus.tiscalinet.it>...

<snip>