This discussion is archived
11 Replies Latest reply: Feb 19, 2013 8:23 PM by jeneesh RSS

Storing an Image in BLOB column of a Table

988852 Newbie
Currently Being Moderated
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
(
ID NUMBER(3) NOT NULL,
PHOTO_NAME VARCHAR2(50),
PHOTO BLOB
);
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;
BEGIN
src_file := bfilename('MSGS3', 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 :  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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points