Easiest way to insert image into oracle database

I have had requirement to store some signature files of USERs in oracle database. So we can display that signature on Letter when we generate reports from BI Publisher. I tried two methods and both worked fine as expected.

Method 1 (Standard way):

First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '<database server path>/temp'

Now copy all the files to that directory.

Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');

Create Procedure to update table with signature file.

CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN

DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;

Now run it with sql plus
SQL> exec user_signature('01')

or in TOAD (sql editor)

begin
user_signature('01');
end;

Method 2 (Easiest way): Need TOAD !!!

Select table from schema browser and click on data tab.



Double click on signature data (HugeBlob) (Hint: Not all cap in BLOB data)
Now you'll see following window. See data length is displayed as zero.
At the top-left corner of window you can see "Load a File" icon. Just click on it and it will ask you for file location.


Once file is loaded you can see data length is displayed as 4664



(Hint: now BLOB data dispalys as in all caps letters i.e HUGEBLOB)

Ohhh, It's done !!! now what ?
If you want to copy file on your local hard disk which is stored in oracle database then just do double-click on BLOB data and click on "Save to File" icon at top-left side of window (second icon).