Search This Blog

Wednesday, August 12, 2009

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')


Following is how data is stored in the database

5 Comments:

abhay said...

it's very important but if
i want to store a new picture
with saving it into directory place
how i do it?

Suresh Vaishya said...

Abhay .. I am sorry can you please be a little clear with your question.

I have also posted another topic where the images are not stored in database but is displayed from a directory. Link Print Image using Reports 6i using the file path

chika said...

can u tell me how to copy images to unix using utl_file

Anonymous said...

Is there any way to do where the directory is not on oracle database because I do not have access to unix and the oracle admin doesn't allow to read or write on the server side except import and export.

Anonymous said...

hi,

its very useful. i have multiple files in directory den how can i store multiple files at same time in oracle table

Copyright (c) All rights reserved. Presented by Suresh Vaishya