11 Replies Latest reply on Feb 20, 2013 4:32 AM by user10903866

    Storing an Image in BLOB column of a Table

      This is regarding saving an Image file a table temp_photo.

      IN LINUX I have created the directory object.
      Step No. 1
      SQL>create directory MSGS3 as '/home/mh/om4000/I_MESSAGES' ;
      Directory created.

      Step 2 :Created the temp_photo Table.

      SQL> create table temp_photo
      Table created.

      Step: 3 The following is the procedure which I have created to load the file from the filesystem to the database.

      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;
      src_file := bfilename('MSGS3', 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 :  When I test it by running the procedure using following:
      SQL> execute load_file(1,'Omega5000.jpg') ;

      I am getting the following error:

      Error starting at line 5 in command:
      execute load_file(1,'Omega5000.gif')
      Error report:
      ORA-22288: file or LOB operation FILEOPEN failed
      No such file or directory
      ORA-06512: at "SYS.DBMS_LOB", line 805
      ORA-06512: at "MH.LOAD_FILE", line 23
      ORA-06512: at line 1
      22288. 00000 - "file or LOB operation %s failed\n%s"
      *Cause:    The operation attempted on the file or LOB failed.
      *Action:   See the next error message in the error stack for more detailed
      information. Also, verify that the file or LOB exists and that
      the necessary privileges are set for the specified operation. If
      the error still persists, report the error to the DBA.

      It says no such file or directory, but I have double checked that file is in the same directory and directory is physically there ( for which I have created the directory object above. )
      Can anyone help me where I am doing something wrong. This is the first time I am using BLOB in my table. Can anyone suggest what is wrong here.