Insert an Image File into Oracle Database

Pictures are very easy to add to a Oracle Table. We have two ways to loading BLOBs and CLOBs into tables.

One using PL/SQL and DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database
internal LOB structures.
Second uses OCI (Oracle Call Interface) to success same objetive. Here we will use DBMS_LOB package.

We follow next steps:

1) Create a directory in database. First of all, we create a directory on the database. The user must be granted the create any directory privilege.

SQL> create directory pictures as ‘c:\my_photos’;
2) Then we create a table for insert pictures in our table . Here we have to use a BLOB to insert the image .

SQL> create table temp_photo
(
ID NUMBER(3) NOT NULL,
PIC_NAME VARCHAR2(50),
PICTURE BLOB
);
3) Next we write a procedure to insert the image in the table.

create or replace PROCEDURE load_file (
p_id number,
p_photo_name in varchar2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename(‘PIC_DIR’, p_photo_name);
— insert a NULL record to lock
INSERT INTO temp_photo
(id, photo_name, photo)
VALUES
(p_id , p_photo_name ,EMPTY_BLOB())
RETURNING photo INTO dst_file;
— lock record
SELECT photo
INTO dst_file
FROM temp_photo
WHERE id = p_id
AND photo_name = p_photo_name
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 temp_photo
SET photo = dst_file
WHERE id = p_id
AND photo_name = p_photo_name;
— close file
dbms_lob.fileclose(src_file);
END load_file;
/

4) Now test it:

SQL> execute load_file(1,’mypicture.jpg’) ;

note: mypicture.jpg must exist on c:\my_photos

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s