Hi, im using SQL Developer Version 3.2.20.09,
I want to create a table with BLOB column for inserting image files. So I created the table, directory and procedure as I described below. And in the C:\ drive I created a new folder and named it image_dir which contains a file named test_image with .jpeg extension.
These are the steps I took:
create directory image_dir as 'c:\image_dir';
CREATE TABLE test_image
(ID NUMBER,
image_filename VARCHAR2(50),
image BLOB
);
CREATE OR REPLACE PROCEDURE insert_image_file (p_id 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 test_image
(ID, image_filename, image
)
VALUES (p_id, p_image_name, EMPTY_BLOB ()
)
RETURNING image
INTO dst_file;
SELECT image
INTO dst_file
FROM test_image
WHERE ID = p_id AND image_filename = 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 test_image
SET image = dst_file
WHERE ID = p_id AND image_filename = p_image_name;
DBMS_LOB.fileclose (src_file);
END insert_image_file;
But I always get this error whenever i run the command EXECUTE insert_image_file (1, 'test_image.jpg'); Please what could possibly be the reason for this and a better solution?
Error starting at line 1 in command:
EXECUTE insert_image_file (1, 'test_image.jpg')
Error report:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 744
ORA-06512: at "ANSUWEB.INSERT_IMAGE_FILE", line 20
ORA-06512: at line 1
- 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.