I tried the below procedure mentioned by Robert Hanrahan to insert/extract data into/from LONG RAW column. When I insert a document and retrieve then it works fine. But when I try to extract a document which is loaded by an application into database then it gives lot of junk characters in the word document. Is there any thing I should add to the below to avoid that.

Regards, Sam


-- create a directory in the database called "MY_FILES"

CREATE OR REPLACE DIRECTORY MY_FILES AS '/app/oracle/admin/FMS9_C/utl';
GRANT READ, WRITE ON DIRECTORY MY_FILES TO CFMS9 WITH GRANT OPTION;
GRANT READ, WRITE ON DIRECTORY MY_FILES TO PUBLIC WITH GRANT OPTION;

-- create a table that holds a BLOB column:

CREATE TABLE DEMO
(
ID INTEGER,
DESCRIPTION VARCHAR2(100 BYTE) DEFAULT 'description:',
THEBLOB BLOB
)
TABLESPACE FAXOUT2
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
LOB (THEBLOB) STORE AS
( TABLESPACE FAXOUT2
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 10M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL;

ALTER TABLE DEMO ADD (
PRIMARY KEY (ID)
USING INDEX
TABLESPACE FAXOUT2
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
));

CREATE OR REPLACE PUBLIC SYNONYM DEMO FOR DEMO;

-- package "BLOBIT"

CREATE OR REPLACE package BLOBIT as

/*
|| Package to manage BLOBS - rjh 2005
|| 1: load_a_file - loads a binary file into BLOB column of table
|| 2: save_to_file - saves a BLOB from table to a file
|| rjh 2005
*/

procedure load_a_file ( p_id in number, p_filename in varchar2 );
procedure save_to_file ( vid in integer, p_filename in varchar2 );

end BLOBIT;
/


CREATE OR REPLACE package body BLOBIT AS

procedure LOAD_A_FILE
( p_id in number, p_filename in varchar2 )
as
l_clob blob;
l_bfile bfile;
begin
insert into demo values ( p_id, default, empty_blob() )
returning theblob into l_clob;
l_bfile := bfilename( 'MY_FILES', p_filename );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );

commit;

end LOAD_A_FILE;

procedure SAVE_TO_FILE
( vid in integer, p_filename in varchar2 )
as

vblob blob;
i2 number;
amt number := 10000;
len number;
my_vr raw(10000);
l_output utl_file.file_type;
p_dir varchar2(30) default 'MY_FILES';
p_file varchar2(30) := p_filename;
begin

l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
for l_cur in (SELECT theblob mylob FROM demo where id = vid)
loop

len := DBMS_LOB.GETLENGTH(l_cur.mylob);
vblob := l_cur.mylob ;
dbms_output.put_line('Length of the Column : ' || to_char(len));
i2 := 1;
if len < 10000 then
DBMS_LOB.READ(vblob,len,i2,my_vr);
utl_file.put(l_output, my_vr );
else
DBMS_LOB.READ(vblob,amt,i2,my_vr);
utl_file.put(l_output, UTL_RAW.CAST_TO_VARCHAR2(my_vr) );
end if;

i2 := i2 + amt;
while (i2 < len) loop
dbms_output.put_line('i2 : ' || to_char(i2));
DBMS_LOB.READ(vblob,amt,i2,my_vr);
utl_file.put(l_output, UTL_RAW.CAST_TO_VARCHAR2(my_vr) );
utl_file.fflush(l_output);
i2 := i2 + amt ;

end loop;
utl_file.fclose(l_output);
end loop;

commit;

end SAVE_TO_FILE;
end;
/

grant execute on BLOBIT to public;
create or replace public synonym BLOBIT for BLOBIT;

-- usage: put a binary file in the directory:
-- cp binary_file.pdf /app/oracle/admin/FMS9_C/utl/binary_file.pdf
-- put the pdf inside the BLOB column:

SQL> exec BLOBIT.load_a_file(1,'binary_file.pdf');

-- retrieve the BLOB back to the file, delete the file in the filesystem:
-- rm /app/oracle/admin/FMS9_C/utl/binary_file.pdf

SQL> exec BLOBIT.save_to_file(1,'binary_file.pdf');