Tuesday, May 8, 2012

Insert an Image File into Oracle Database

Photographs and pictures and Oracle BLOB data are easy to add to a 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 folowing 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 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,
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;
  src_file := bfilename('PHOTO_DIR', p_photo_name);
  -- insert a NULL record to lock
  INSERT INTO temp_photo
  (id, photo_name, photo)
  (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
  -- 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
 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 .

Enjoy    :-)


Anonymous said...

Thank you! Very helpful!

Anonymous said...

Hello, Please tell me how retrieve the store image and thanks for your "insert an image blog......"

abhi said...

HI,thank you for code.
but iam facing following error when try to execute ur procedure.
Iam using it on windows platform with oracle 11g.
Error starting at line 1 in command:
execute load_file(1,'gif.jpg')
Error report:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 744
ORA-06512: at "TUSER.LOAD_FILE", line 23
ORA-06512: at line 1
22285. 00000 - "non-existent directory or file for %s operation"
*Cause: Attempted to access a directory that does not exist, or attempted
to access a file in a directory that does not exist.
*Action: Ensure that a system object corresponding to the specified
directory exists in the database dictionary, or
make sure the name is correct.

Please help me to find solution...Thanq.

shan kumar said...

hahahahha very nice
we can upload images into database like this pls tell me