Assuming you have the following Oracle table:

<pre lang="sql">create table icon(id number(19), icon blob);

these are 2 methods I found for inserting the value of a file in the icon.icon column.

Method 1

This is the most commonly found method:

<pre lang="sql">CREATE OR REPLACE PROCEDURE load_icon (imagefile VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
icon_id NUMBER(19,0);
BEGIN
src_file := bfilename('TEMP', imagefile);

-- insert a NULL record to lock
INSERT INTO icon (ID, ICON)
VALUES
(icon_sequence.nextval, EMPTY_BLOB())
RETURNING icon INTO dst_file;

select icon_sequence.currval into icon_id from dual;

-- lock record
SELECT icon
INTO dst_file
FROM icon
WHERE id = icon_id
FOR UPDATE;

-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

-- determine length
lgh_file := dbms_lob.getlength(src_file);

-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

-- update the blob field
UPDATE icon
SET icon = dst_file
WHERE id = icon_id;

-- close file
dbms_lob.fileclose(src_file);
END load_icon;
/

This method however has the disadvantage that it requires the image to be placed in a special folder which is mapped in Oracle using the CREATE DIRECTORY syntax. This also requires the CREATE DIRECTORY grant and physical access to the Oracle server not to mention that is not easy to automatize via a sql script.

<pre lang="sql">--give CREATE DIRECTORY grant to current user
CREATE DIRECTORY TEMP AS '/tmp';
--show directories
SELECT * FROM DBA_DIRECTORIES;
--manualy copy a.png to /tmp
call load_icon('a.png');

Method 2

This method is much better since it requires only an insert:

<pre lang="sql">insert into icon(ID, ICON) values (icon_sequence.nextval, utl_encode.base64_decode(utl_raw.cast_to_raw('iVBORw0......TkSuQmCC')));

The idea is to represent the blob as it’s base64 representation. It’s very simple to use for small files but for large files one must fall back on the first method.

An easy way to generate the sql can be:

<pre lang="bash">for i in $(ls *.png); do echo "insert into icon(ID, ICON) values (icon_sequence.nextval, utl_encode.base64_decode(utl_raw.cast_to_raw('$(base64 $i)')));" >> load_icon.sql; done