Nowadays when we have a BLOB column to be loaded with the content of a given file, we do :

INSERT INTO table(columnPK, column2...columnBLOB) values (PKidtobeinserted, ...., empty_blob()) RETURNING PKidvalue;

and later we call a procedure called PROC_LOAD_BLOB(PKidnserted, 'pathandnameofthefile'), who calls DBMS_LOB.OPEN, then calls DBMS_LOADFROMFILE and closes the BLOB with DBMS_LOB.CLOSE, such as :

EXEC PROC_LOAD_BLOB(PKinserted, 'pathandnameofthefile');

ie, first we insert the record and later the procedure do the load..

Would exist some (easy, without a AFTER INSERT trigger and without dynamic SQL, GTTs, etc) option to do all in one-pass, and right on the SQL INSERT command, in the sense of :

INSERT INTO table(columnPK, column2...columnBLOB) values (PKidtobeinserted, ...., PROCn ('pathandnameofthefile');

?? What we could use in PROCn to do this ?

Main issue here is, the record is not inserted yet, so we do not have yet the BLOB locator to be the target for LOADFROMFILE....

Regards,

J. Laurindo Chiappa

SQL> create or replace
  2  function loader(p_filename varchar2) return blob is
  3    bf bfile := bfilename('TEMP',p_filename);
  4    b blob;
  5  begin
  6    dbms_lob.createtemporary(b,true);
  7    dbms_lob.fileopen(bf, dbms_lob.file_readonly);
  8    dbms_lob.loadfromfile(b,bf,dbms_lob.getlength(bf));
  9    dbms_lob.fileclose(bf);
 10    return b;
 11  end;
 12  /

Function created.

SQL> create table t ( blobcol blob );

Table created.

SQL>
SQL> insert into t select loader('12c.png') from dual;

1 row created.

SQL> select dbms_lob.getlength(blobcol) from t;

DBMS_LOB.GETLENGTH(BLOBCOL)
---------------------------
                     134220

SQL>