Oracle – Loading a BLOB from File

Here is a simple example of loading BLOB content from disk. First, the operating user will need a grant to enable the creation of an Oracle DIRECTORY object: GRANT CREATE ANY DIRECTORY TO testuser;

Next, the user will need to create the DIRECTORY object that points to the location of the source file on disk (in this case, C:\dbload\): CREATE OR REPLACE DIRECTORY test_dir AS 'C:\dbload';

Finally, below is an example of loading test_blob.dat from disk and inserting it into the test_blob_tab table:

SQL> DESC test_blob_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATA                                               BLOB

SQL> DECLARE
  2
  3    insert_blob BLOB;
  4
  5    FUNCTION load_blob( filename VARCHAR2 )
  6    RETURN BLOB
  7    AS
  8      temp_blob BLOB;
  9      file BFILE;
 10      src_offset NUMBER DEFAULT 1;
 11      dest_offset NUMBER DEFAULT 1;
 12    BEGIN
 13
 14      DBMS_LOB.CREATETEMPORARY( temp_blob, FALSE, DBMS_LOB.SESSION );
 15
 16      file := BFILENAME( 'TEST_DIR', filename );
 17      DBMS_LOB.OPEN( file, DBMS_LOB.LOB_READONLY );
 18
 19      DBMS_LOB.LOADBLOBFROMFILE( temp_blob, file,
 20        DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset );
 21
 22      DBMS_LOB.CLOSE( file );
 23
 24
 25      RETURN temp_blob;
 26
 27    END;
 28
 29  BEGIN
 30
 31    insert_blob := load_blob( 'test_blob.dat' );
 32    INSERT INTO test_blob_tab ( data ) VALUES ( insert_blob );
 33
 34  END;
 35  /

PL/SQL procedure successfully completed.

Advertisements