rem -----------------------------------------------------------------------
rem Filename:   loadlob.sql
rem Purpose:    Load a binary file (images, documents, etc) into a
rem             database table.
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on
DROP TABLE lob_table;
DROP SEQUENCE lob_seq;

CREATE OR REPLACE DIRECTORY my_dir AS '/app/oracle/';

CREATE TABLE lob_table (id NUMBER, fil BLOB);
CREATE SEQUENCE lob_seq;

CREATE OR REPLACE PROCEDURE load_file(p_file VARCHAR2)
IS
    src_lob  BFILE := BFILENAME('MY_DIR', p_file);
    dest_lob BLOB;
BEGIN
   INSERT INTO lob_table VALUES(lob_seq.nextval, EMPTY_BLOB())
      RETURNING fil INTO dest_lob;

   DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                          SRC_LOB  => src_lob,
                          AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
   DBMS_LOB.CLOSE(src_lob);

   COMMIT;
END;
/
show errors

-- Let's test it
exec load_file('pic1.gif');
SELECT id, DBMS_LOB.GETLENGTH(fil) AS bytes_loaded
  FROM lob_table;


rem -----------------------------------------------------------------------
rem Filename:   savelob.sql
rem Purpose:    Save a binary file (images, documents, etc) from database
rem             to a flat file.
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE save_file(p_id NUMBER, p_file VARCHAR2)
IS
   v_lob_loc      BLOB;
   v_lob_len      NUMBER;
   v_buffer       RAW(32767);
   v_buffer_size  BINARY_INTEGER := 32767;
   v_offset       NUMBER         := 1;
   v_out_file     UTL_FILE.FILE_TYPE;
BEGIN
    SELECT fil INTO v_lob_loc FROM lob_table WHERE id = p_id;
    v_lob_len := DBMS_LOB.GETLENGTH(v_lob_loc);

    DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);
    v_out_file := UTL_FILE.FOPEN(location      => 'MY_DIR', 
                                 filename      => p_file, 
                                 open_mode     => 'w',
                                 max_linesize  => 32767);

    WHILE (v_offset <= v_lob_len) LOOP
      dbms_output.put_line('v_start : ' || to_char(v_offset));
      DBMS_LOB.READ(lob_loc => v_lob_loc,
                    amount  => v_buffer_size,
                    offset  => v_offset,
                    buffer  => v_buffer);
      v_offset := v_offset + v_buffer_size;
      UTL_FILE.PUT_RAW(file   => v_out_file,
                       buffer => v_buffer);
    END LOOP;

    UTL_FILE.FCLOSE(v_out_file);
    DBMS_LOB.CLOSE(v_lob_loc);
END;
/
show errors

-- Let's test it
exec save_file(1, 'pic2.gif');
! ls -l /app/oracle/pic*.gif