| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Problem Oracle windows 9.2, uploading and downloading file to blob column
Hi I have a problem, I think this is a bug, I copied this from an example in
asktom
I up a file 880k and download another 883k, obviously this doesn't open
Any idea?
create table demo
( id int primary key,
theBlob blob
)
/
drop directory my_files ;
create or replace directory my_files as 'c:\';
--uploading the file
declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() ) returning theBlob into l_blob;
l_bfile := bfilename( 'MY_FILES', 'a.pdf' ); dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
end;
/
--recreating the file
declare
vblob blob;
vstart number:=1;
bytelen number := 32000;
len number;
my_vr raw(32000);
l_output utl_file.file_type;
p_dir varchar2(30) default 'MY_FILES';
p_file varchar2(30) default 'b.pdf';
begin
l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
for l_cur in (SELECT theblob mylob FROM demo)
loop
len := DBMS_LOB.GETLENGTH(l_cur.mylob);
vblob := l_cur.mylob ;
dbms_output.put_line('Length of the Column : ' || to_char(len));
vstart := 1;
while (vstart < len) loop -- loop till entire data is fetched
dbms_output.put_line('vstart : ' || to_char(vstart));
DBMS_LOB.READ(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
vstart := vstart + bytelen ;
end loop;
utl_file.fclose(l_output);
end loop;
exception when others then
utl_file.fclose(l_output);
dbms_output.put_line(sqlerrm);
end ;
 |
 |