PL/SQL: Sample code to insert BLOB into Oracle database table.

This post explains how to insert a blob object into database table programmatically.

• Create a folder/directory (ex:- C:\test )manually in the file system.
• Create a directory object in the database by connecting as SYSDBA and grant the access to custom schema(ex:- HR)

 CONN / AS SYSDBA;  
 CREATE OR REPLACE DIRECTORY images AS 'C:\test';  
 GRANT READ, WRITE ON DIRECTORY images TO HR;

• Create the table as below in the custom schema (ex:-HR)

 CREATE TABLE IMAGE_STORE  
  (  
   IMAGE_ID NUMBER ,  
   FILE_NAME VARCHAR2(200 BYTE) ,  
   IMAGE BLOB,  
   MIME_TYPE VARCHAR2(100 BYTE),  
   CONSTRAINT IMAGE_STORE_PK PRIMARY KEY (IMAGE_ID)  
  );

• Copy a test file (Test_File.pdf) into the directory ('C:\test') mentioned in the above script.
• Now connect to the custom schema (HR) and execute the below code

 DECLARE  
  src_lob BFILE := BFILENAME('IMAGES', 'Test_File.pdf');  
  dest_lob BLOB;  
 BEGIN  
  INSERT INTO image_store VALUES (1,'Test_File.pdf', EMPTY_BLOB(), 'application/pdf')  
  RETURNING image INTO dest_lob;  
  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);  
  DBMS_LOB.LoadFromFile(   
              DEST_LOB => dest_lob,  
              SRC_LOB => src_lob,  
              AMOUNT  => DBMS_LOB.GETLENGTH(src_lob) );  
  DBMS_LOB.CLOSE(src_lob);  
  COMMIT;  
 END;  
 /  

Feel free to point out if anything is missing/wrong in this blog.