Inserting image to table...ORA-22288: file or LOB operation FILEOPEN failed
909056Jan 5 2012 — edited Jan 5 2012Good day!
I'm just new with using databases, and i'm enjoying it.
So I read that you can insert images to a table, and so i decided to try it... and here's where I'm at..
*I made a directory
CREATE directory image_dir as 'D:\Images';
--Directory Created.
*I created a table
CREATE TABLE animages
(aname VARCHAR2(40),
breedno NUMBER(10),
image_file BLOB,
image_name VARCHAR2(40),
CONSTRAINT aname_fk FOREIGN KEY (aname) REFERENCES clist(aname));
--Table Created.
*Then I made a procedure for inserting the images
CREATE OR REPLACE PROCEDURE insert_image_file (p_aname VARCHAR2, p_breedno NUMBER, p_image_name IN VARCHAR2)
IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := BFILENAME('IMAGE_DIR', p_image_name);
INSERT INTO animages
(aname, breedno, image_file, image_name)
VALUES (p_aname, p_breedno, EMPTY_BLOB(), p_image_name)
RETURNING image_file
INTO dst_file;
SELECT image_file
INTO dst_file
FROM animages
WHERE aname = p_aname AND image_name = p_image_name
FOR UPDATE;
DBMS_LOB.fileopen(src_file, DBMS_LOB.file_readonly);
lgh_file := DBMS_LOB.getlength(src_file);
DBMS_LOB.loadfromfile(dst_file, src_file, lgh_file);
UPDATE animages
SET image_file = dst_file
WHERE aname = p_aname AND image_name = p_image_name;
DBMS_LOB.fileclose(src_file);
END;
--Procedure Created.
*So i was able to do those but when i was trying to execute the procedure i get this error..
execute insert_image_file('African Elephant', 60, 'African_Elephant');
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The device is not ready.
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "SCOTT.INSERT_IMAGE_FILE", line 18
ORA-06512: at line 1
I've been looking for a solution for a day now, hope someone could help me, thanks.
BTW, I got the code for the PROCEDURE from a user named Aparna16, just did some minor editing so it would fit my tables, thanks.
And sorry if the post is too long.