i m using get_file_name built-in on when-button-pressed trigger and save image in database BLOB datatype, every thing is going smoothly but i have a requirement that if load size is more then 25 kb then user unable to save this image with
message ('Image Size is more then 25kb ');
i got some online help can any one exaplain me this code
CREATE TABLE TEMP_PHOTO ( ID NUMBER(3) NOT NULL, PHOTO_NAME VARCHAR2(50), PHOTO BLOB );
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('PHOTO_DIR', 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;
SELECT photo INTO dst_file FROM temp_photo WHERE id = p_id AND photo_name = p_photo_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 temp_photo SET photo = dst_file WHERE id = p_id AND photo_name = p_photo_name;
dbms_lob.fileclose(src_file);
END load_file;
The procedure created successfully
Now,
execute load_file(1,'Sunset.jpg'); gives me the following error :
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 805
ORA-06512: at line 6
Thanks in advance
The online docs have the information about creating directories and about dbms_lob.loadfromfile.
Create directory:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5007.htm#SQLRF01207
dbms_lob.loadfromfile:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lob.htm#ARPLS66733
As for the code itself, I think there are several unnecessary steps involved. There are several examples on the the web if you look around.
Personally I would change the procedure call and go with:
dbms_lob.LOADBLOBFROMFILE
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lob.htm#ARPLS66723
Google around for LOADBLOBFROMFILE and you'll locate many examples.