Home » Data Blocks » Loading Image Data into BLOB Columns in Oracle

Loading Image Data into BLOB Columns in Oracle

You have various JPG, PNG, GIF, and BMP image files that need to be managed and backed up in the
database, and you need a way to do an initial load into the database.

Loading binary LOBs is a bit simpler than loading text-based LOBs.

Create a sequence for your image table’s unique identifier, plus the table itself:

create sequence img_seq;

create table image
img_num number,
img_nm varchar2(100),
img_blb blob,
ins_ts timestamp

Next, run an anonymous block to load the image SCREEN CAPTURE.BMP into your database table:
src_blb bfile; /* point to source BLOB on file system */
dst_blb blob; /* destination BLOB in table */
src_img_nm varchar2(100) := 'Screen Capture.bmp';
src_offset integer := 1; /* where to start in the source BLOB */
dst_offset integer := 1; /* where to start in the target BLOB */
src_blb := bfilename('LOB_SRC',src_img_nm);
insert into image (img_num, img_nm, img_blb, ins_ts)
values(img_seq.nextval, src_img_nm, empty_blob(), systimestamp)
returning img_blb into dst_blb;
dbms_lob.open(src_blb, dbms_lob.lob_readonly);
dest_lob => dst_blb,
src_bfile => src_blb,
amount => dbms_lob.lobmaxsize,
dest_offset => dst_offset,
src_offset => src_offset
dbms_output.put_line('Wrote BLOB to table: ' || src_img_nm);

After running this block, image will be loaded into blob and you can check the record with the following command:

select img_num, img_nm, ins_ts, length(img_blb) from image;