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
|
DECLARE l_blob BLOB; l_bfile BFILE; l_offset_dest INTEGER :=1; l_offset_src INTEGER :=1; BEGIN /* Get a BFILE pointer to OS file. */ SELECT bfilename (‘TMP’,’CLIENT_CUST_BLEUPRINT.xml’) INTO l_bfile FROM dual;
/* Open the BFILE */ DBMS_LOB.FILEOPEN(l_bfile);
/* Initialize the BLOB */ DBMS_LOB.CREATETEMPORARY(l_blob,TRUE);
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 ); update table1 set col1 = l_blob; commit;
/* Close the BFILE */ dbms_lob.FILECLOSE(l_bfile);
end; / |
Vishal Gupta's Blog 
Gus Spier said
What is the ddl for table1? Is this a table with a single column of type BLOB?
Naeeym said
How do I put the FILE in the TMP directory of the Server from a Client?? As I don’t want the Client to have any direct share to Server Directory..
I don’t want to user to Upload the file to the server from their client machine, and ask somebody else to load it from there.
I want the user to insert directly into the BLOB….
Is there any way to use this Procedure from the Client??
I tried, but it seems Oracle does not support client call to DBMS_LOB.
Vishal Gupta said
Gus,
table1 is just an example. It can be any table with a BLOB column. Lets say it has following definition.
CREATE TABLE table1
(COL1 BLOB);
Vishal Gupta said
Nayeem,
Example above was more directed towards DBAs. And dba would normally have access to server. Its using oracle directory object which is a directory on server.
Judy said
All of these acrtiels have saved me a lot of headaches.