Loading File into a Blob
Posted by Vishal Gupta on Sep 23, 2008
Yesterday I received a request from a developer to load file into a BLOB. To be honest I have never loaded a file into a BLOB in past. I had some idea that I would have to use DBMS_LOB pl/sql package to achieve this.
Here are the steps to do this.
1. Create an oracle directory object.
create directory tmp as ‘/tmp’;
2. Load the file into BLOB
l_offset_dest INTEGER :=1;
l_offset_src INTEGER :=1;
/* Get a BFILE pointer to OS file. */
SELECT bfilename (‘TMP’,'CLIENT_CUST_BLEUPRINT.xml’)
/* Open the BFILE */
/* Initialize the BLOB */
DBMS_LOB.LOADBLOBFROMFILE(dest_lob => l_blob
,src_bfile => l_bfile
,amount => DBMS_LOB.LOBMAXSIZE
,dest_offset => l_offset_dest
,src_offset => l_offset_src
set col1 = l_blob;
/* Close the BFILE */