Thanks for the question, Jos� Laurindo.
Asked: February 16, 2018 - 2:04 pm UTC
Last updated: February 20, 2018 - 1:11 am UTC
Version: 11.2.0.4 EE
Viewed 10K+ times! This question is
You Asked
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
and Connor said...
You could do a small wrapper, eg
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>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment