Wednesday, April 2, 2008

Insert image as BLOB into DB using stored procedure.

You need first to declare your package :

CREATE OR REPLACE PACKAGE IMAGE AS
PROCEDURE load(filename VARCHAR2);
PROCEDURE get(name varchar2);
end;
/

then you specify your package
CREATE OR REPLACE PACKAGE BODY IMAGE AS

PROCEDURE load(filename VARCHAR2) AS
f_lob BFILE;
b_lob BLOB;
image_name VARCHAR2(30);
mime_type VARCHAR2(30);
dot_pos NUMBER;
BEGIN
dot_pos := INSTR(filename,'.');
image_name := filename; --SUBSTR(filename,1,dot_pos-1);
mime_type := 'image/'||SUBSTR( filename,dot_pos+1,length(filename) );

INSERT INTO images values(image_name,mime_type,empty_blob() )
RETURN content INTO b_lob;
f_lob := BFILENAME('SOURCE_DIR',filename);
dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
COMMIT;
END;


PROCEDURE get(name varchar2) AS
vblob BLOB;
mime_type VARCHAR2(30);
BEGIN
select content,mime_type
into vblob,mime_type
from images
where image_name=name;
owa_util.mime_header(mime_type, false);
owa_util.http_header_close;
wpg_docload.download_file(vblob);
exception when others then
htp.p(sqlcode || sqlerrm);
END;

END;
/

But this code doesn't seem to fit your need because you said that you would like to store the c:\mydir\myfile.jpg from you computer (client side) into blob field in database (server side), and with this package , you need to create on server side (data base) a source directory (SQL> create directory source_dir as '/mydir'; --if you have an image file in /mydir/toolbar.gif) in which you will store all yours file you need to load in blob field with this kind of statement :
SQL> exec image.load('toolbar.gif');

So with this exemple you can not load 'client-side' file in 'server-side' blob field, and AS ME you would like to load from client side , if someone would have a suggestion, he/she would answer to a problem which concern 2 persons now.

0 comments: