Problem Oracle windows 9.2, uploading and downloading file to blob column

  • From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 25 Feb 2004 17:18:42 -0400

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

-- get the blob locator

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 ;


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  • » Problem Oracle windows 9.2, uploading and downloading file to blob column