Insert BLOB image file in oracle database table

Here we will discuss how to insert BLOB file in the database. For this we will create a table and then a procedure that will be used to insert records in the table.

Use following script to create an employee table

CREATE TABLE SV_EMP_PHOTO
(
ID NUMBER(3) NOT NULL,
PHOTO_NAME VARCHAR2(40),
PHOTO_RAW BLOB,
EMP_NAME VARCHAR2(80)
)

Create a directory where the photos will be stored. I am creating a directory in UNIX as our database is created in UNIX.

Create directory SV_PHOTO_DIR as '/u002/app/applmgr/empphoto'

Script to create a procedure SV_LOAD_IMAGE that will insert records in the table.

CREATE OR REPLACE PROCEDURE sv_load_image (p_id NUMBER,
p_emp_name IN VARCHAR2,
p_photo_name IN VARCHAR2)
IS
l_source BFILE;
l_dest BLOB;
l_length BINARY_INTEGER;
BEGIN
l_source := BFILENAME ('SV_PHOTO_DIR', p_photo_name);

INSERT INTO sv_emp_photo (ID,
photo_name,
emp_name,
photo_raw)
VALUES (p_id,
p_photo_name,
p_emp_name,
EMPTY_BLOB ())
RETURNING photo_raw
INTO l_dest;

-- lock record
SELECT photo_raw
INTO l_dest
FROM sv_emp_photo
WHERE ID = p_id AND photo_name = p_photo_name
FOR UPDATE;

-- open the file
DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
-- get length
l_length := DBMS_LOB.getlength (l_source);
-- read the file and store in the destination
DBMS_LOB.loadfromfile (l_dest, l_source, l_length);

-- update the blob field with destination
UPDATE sv_emp_photo
SET photo_raw = l_dest
WHERE ID = p_id AND photo_name = p_photo_name;

-- close file
DBMS_LOB.fileclose (l_source);
END --sv_load_image;

/

I have copied few .jpg images in /u002/app/applmgr/empphoto in UNIX.
Execute the procedure as follows to create record in database

EXEC sv_load_image(1,'Pavki','one.jpg')
EXEC sv_load_image(2,'Suresh','two.jpg')
EXEC sv_load_image(3,'Rachna','three.jpg')