Reading blobs

From: <msherr_at_my-dejanews.com>
Date: Mon, 10 Aug 1998 20:53:22 GMT
Message-ID: <6qnmk2$l90$1@nnrp1.dejanews.com>

I've been attempting to export data stored internally in the Oracle database as a blob to a file. I've used the following code to insert the blob, and it seems to run correctly:

  declare
    l_bfile bfile;
    l_blob blob;
  begin
    insert into fa_templates values ( 3,'hi',6,'this is a test',empty_blob() )     return fa_template_data into l_blob;     l_bfile := bfilename( 'MY_FILES','A.TXT');     dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile) );     commit;
  end;

I've selected the first field from the fa_templates file, and it appears as if the entry has been appended.

The problem occurs when I then try to retrieve the data. I've been using this code:

create or replace procedure test1 IS
file_handle utl_file.file_type;

  l_lob blob;
  l_amt number default 30;
  l_off number default 1;
  l_raw raw(4096);
  l_var varchar2(5000);

begin
file_handle := utl_file.fopen('C:\TEMP','micah.txt','w'); utl_file.put_line( file_handle, 'this is a test' ); select fa_template_data into l_lob from fa_templates where fa_template_id=3; begin

	loop
		dbms_lob.read( l_lob, l_amt, l_off, l_raw );
	        l_var := utl_raw.cast_to_varchar2( l_raw );
		utl_file.put_line( file_handle, utl_raw.cast_to_varchar2( l_raw
) );
		l_off := l_off + l_amt;
		l_amt := 4096;
	end loop;
     EXCEPTION
           WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.PUT_LINE('no_data_found');
     end;

utl_file.fclose(file_handle);
EXCEPTION

           WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.PUT_LINE('no_data_found');
              UTL_FILE.FCLOSE(file_handle);
           WHEN UTL_FILE.INVALID_PATH THEN
              DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
              UTL_FILE.FCLOSE(file_handle);
           WHEN UTL_FILE.READ_ERROR THEN
              DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
              UTL_FILE.FCLOSE(file_handle);
           WHEN UTL_FILE.WRITE_ERROR THEN
              DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
              UTL_FILE.FCLOSE(file_handle);
           WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('other stuff');
              UTL_FILE.FCLOSE(file_handle);
         END;

The procedure compiles without any problems. Unfortunately, when I execute it, I either get the 'no_data_found' or 'other stuff' error message.

Any help would be gratefully appreciated. Please cc replies to msherr_at_obs-us.com.

Thanks,
Micah

Open Book Systems ----------------- Micah Sherr, Programmer/SysOp msherr_at_obs-us.com 37J Whistlestop Mall phone: (978)546-7346 Rockport, MA 01966 fax: (978)546-9807

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Aug 10 1998 - 15:53:22 CDT