Insert an Image File into Oracle Database

Insert an Image File into Oracle Database

Photographs and pictures and Oracle BLOB data are easy to add to an Oracle table. There are two ways to load BLOBs and CLOBs into the database. The first method uses PL/SQL and the DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database internal LOB structures. The second uses the Oracle Call Interface (OCI) to perform the same function. Here, we will use the first method. For inserting an image, we follow the following steps : 

Step 1 : First, we need to create a directory on the database (which is mapped to a directory in the server's filesystem). The user must be granted the create any directory privilege.
SQL>create directory photo_dir as 'c:\photo_dir' ;
Directory created.

Step 2 : Then we need to create a table which is used by the procedure to insert the image in our table. Here we have to use a BLOB to insert the image .
SQL> create table temp_photo
 (
 ID    NUMBER(3)     NOT NULL,
 PHOTO_NAME      VARCHAR2(50),
 PHOTO    BLOB
 );
Table created.

Step 3 : Now let's write the procedure to insert the image in the table above.

SQL> create or replace PROCEDURE load_file (
 p_id number,
 p_photo_name in varchar2) IS
 src_file BFILE;
 dst_file BLOB;
 lgh_file BINARY_INTEGER;
 BEGIN
  src_file := bfilename('PHOTO_DIR', p_photo_name);
  -- insert a NULL record to lock
  INSERT INTO temp_photo
  (id, photo_name, photo)
  VALUES
  (p_id , p_photo_name ,EMPTY_BLOB())
  RETURNING photo INTO dst_file;
  -- lock record
  SELECT photo
  INTO dst_file
  FROM temp_photo
  WHERE id = p_id
  AND photo_name = p_photo_name
  FOR UPDATE;
  -- open the file
  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
  -- determine length
  lgh_file := dbms_lob.getlength(src_file);
  -- read the file
  dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
  -- update the blob field
  UPDATE temp_photo
  SET photo = dst_file
  WHERE id = p_id
  AND photo_name = p_photo_name;
  -- close file
  dbms_lob.fileclose(src_file);
 END load_file;
/


Step 4 :  We can test it from SQL*Plus
SQL> execute load_file(1,'rdht.jpg') ;


Note : Remember that the file rdht.jpg should exist in the server's 'c:\photo_dir' directory.