Friday, January 8, 2010

Using PLSQL to load images into a BLOB

create or replace directory temp as '/home/oracle';

CREATE OR REPLACE PROCEDURE load_file (dirname VARCHAR2, filename VARCHAR2) IS

src_file BFILE;
dst_file BLOB;

BEGIN
dbms_lob.createtemporary(dst_file,true);
src_file := bfilename(dirname, filename);
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
dbms_lob.loadfromfile(dst_file, src_file, dbms_lob.getlength(src_file));

UPDATE mytable
SET image_data = dst_file
WHERE image_id=741;

dbms_lob.fileclose(src_file);
END;
/

exec load_file('TEMP','testimage.png');