-- a short function to load a picture


CREATE TABLE my_pictures(
PIC_ID NUMBER,
name   VARCHAR2(100),
photo BLOB,
CONSTRAINT pic_pk PRIMARY KEY(PIC_ID ));

-- The unix-user "oracle" must have read-permissions on this directory
CREATE OR REPLACE DIRECTORY PICTURES AS '/u01/app/oracle/product/10g/demo';
-- If an other user will read Pictures
-- GRANT READ, WRITE ON DIRECTORY PICTURES TO <USER>;

create sequence mysequenz;


-- Input-parameter is only the filename of the picture
-- Returnvalue is the id of thre picture.
-- the Path is definied in "Create Direcory.."

CREATE OR REPLACE function load_pictures( pic_name varchar2) return number
AS
v_source BFILE;
v_target BLOB;
v_ret number;
BEGIN
select mysequenz.nextval into v_ret from dual;

-- Initialize the BLOB
INSERT INTO my_pictures
VALUES(v_ret, pic_name , empty_blob())
RETURNING photo INTO v_target;

v_source := bfilename('PICTURES', pic_name);

DBMS_LOB.FILEOPEN( v_source , DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE(v_target, v_source ,DBMS_LOB.GETLENGTH( v_source ));
DBMS_LOB.FILECLOSE(v_source);


  -- COMMIT; -- You can commmit in the Form if you like
  return v_ret;
-- todo write exception handler
END;
/
--Test function  in sqlplus
var x number
exec :x := load_pictures( 'ipcc_rbz.jpg');
print