Insert a File as BLOB Using DBMS_LOB

Following script could be used to insert a file as a BLOB into a table. The file to be inserted must reside in the location referred by the database directory LOADF. File name in this case data.log. File is inserted to table called lobins which only has single column which is of BLOB type.

declare

  file_name varchar2(100) := 'data.log';
  db_dir_name varchar2(100) := 'LOADF';

  dest_loc  BLOB := empty_blob();
  src_loc   BFILE := BFILENAME(db_dir_name, file_name);
  destoff number := 1;
  srcoff number :=1;

begin

  DBMS_LOB.OPEN(src_loc, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.CREATETEMPORARY(lob_loc => dest_loc, cache => false);
  DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.LOADBLOBFROMFILE (dest_loc,src_loc,DBMS_LOB.getLength(src_loc),destoff,srcoff);

  insert into lobins values (dest_loc);
  commit;

  DBMS_LOB.CLOSE(dest_loc);
  DBMS_LOB.CLOSE(src_loc);

  end;
  /