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:
declare 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 */ begin 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); dbms_lob.loadblobfromfile ( dest_lob => dst_blb, src_bfile => src_blb, amount => dbms_lob.lobmaxsize, dest_offset => dst_offset, src_offset => src_offset ); dbms_lob.close(src_blb); commit; dbms_output.put_line('Wrote BLOB to table: ' || src_img_nm); end;
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;