Insert BLOB image file in oracle database table

Step 1:

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


Step2:

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

CREATE OR REPLACE DIRECTORY
SV_PHOTO_DIR AS
'/usr/tmp/SV_PHOTO_DIR';


Step 3:

Copy the image file from local machine to the above created Directory using FTP.
Kindly note that Report 6i does not support to all image formats. I used here .bmp file format.


Step4:

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

/* Formatted on 2009/11/30 11:12 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE apps.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;


Step5:

exec sv_load_image(1,'Mr. Kamlesh Nikhade','Kamlesh.bmp');

Step6 :

select * from SV_EMP_PHOTO;




Display Image In report 6i

We will use same SV_EMP_TABLE as a reference and using Reports 6i create a report that prints ID Card. For simplicity there are no parameters to the report and it will create ID cards for all the employees for which record exists in the table.
In the data model create following SQL Query.

Step 1 :

SELECT ID, PHOTO_NAME, PHOTO_RAW ,emp_name
FROM SV_EMP_PHOTO;



Step2:

The next step is to create a layout for the report. Screenshot below.




The field that stores the value of BLOB column in the properties for the field remember to change the file format to image,
This image data will be retrieved from database and will change with record.
Procedure : Take One text field=>properties=>file format=>image.

Step 3:
Compile & Run the report.