Script to Load file from Directory to Blob column

create or replace PROCEDURE load_file(
    pi_id  IN INTEGER,
    pfname IN
VARCHAR2)
IS
  SRC_FILE bfile;
  dst_file BLOB:=
EMPTY_BLOB();
  lgh_file
BINARY_INTEGER;
BEGIN
  src_file :=
bfilename(‘dir’, pfname);
  INSERT
  INTO image_store
   
(id_pk,fname,image)
    VALUES
   
(pi_id,pfname,EMPTY_BLOB())
  RETURNING image
  INTO dst_file;
 
DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
  lgh_file :=
dbms_lob.getlength(src_file);
 
DBMS_LOB.LOADFROMFILE(DST_FILE, SRC_FILE, LGH_FILE);
 
–DBMS_LOB.FREETEMPORARY(dst_file);– ADDED BY RAJAN
  COMMIT;
 
dbms_lob.close(src_file);
END load_file;

  • September 28, 2018 | 20 views