Re: Insert a Blob from a file.

From: Prakash <venkatprakash_at_my-deja.com>
Date: Tue, 14 Dec 1999 23:03:35 GMT
Message-ID: <836ic4$rmr$1@nnrp1.deja.com>

Try this. Credit to one of my friends who sent me this. And this works for me also.

set serveroutput on;
CREATE OR REPLACE PROCEDURE retrieve_lob IS

      temp_blob           BLOB;
      data_buffer         RAW (1);
      temp_buffer         VARCHAR2(1);
      amount              BINARY_INTEGER := 1;
      position            INTEGER := 1;
      filehandle          utl_file.file_type;
      error_number        NUMBER;
      error_message       VARCHAR2(100);
      length_count        INTEGER;

  BEGIN
      SELECT resume INTO temp_blob FROM resume WHERE resume_id = 1;
      length_count := dbms_lob.getlength(temp_blob);
      dbms_output.put_line('Internal LOB size is:  ' || length_count);
      filehandle := utl_file.fopen('\',
                                   'lob_flat.out','W');
      WHILE length_count <> 0 LOOP
         dbms_lob.read (temp_blob, amount, position, data_buffer);
         temp_buffer := utl_raw.cast_to_varchar2(data_buffer);
         utl_file.put (filehandle, temp_buffer);
         position := position + 1;
         length_count := length_count - 1;
         data_buffer := null;
      END LOOP;

      dbms_output.put_line('Exit the loop');
      utl_file.fclose(filehandle);
      dbms_output.put_line('Close the file');
  EXCEPTION
	WHEN UTL_FILE.INVALID_PATH THEN
	  dbms_output.put_line('Invalid Path');
	  utl_file.fclose_all;
      WHEN OTHERS THEN
         BEGIN
            error_number := sqlcode;
            error_message := substr(sqlerrm ,1 ,100);
            dbms_output.put_line('Error #: ' || error_number);
            dbms_output.put_line('Error Message: ' || error_message);
            utl_file.fclose_all;
         END;

  END; /
show errors;
exec retrieve_lob

If anybody familiar with using LOB with remote database please help me reading my post.

THanks

V Prakash

In article <384BAE66.7FB8ADF9_at_dnet.it>,   Thomas Plant <tomspost_at_dnet.it> wrote:
> Hello everybody,
>
> I need to insert into a BLOB-Field an external binary-file (a picture
> for example). How do I accomplish this in PL/SQL, and how do I extract
> it after I've inserted it ?
>
> I'm using Oracle 8.0.5.1 EE on Linux.
>
> Thanks for every suggestion,
> Thomas Plant
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 14 1999 - 17:03:35 CST