declare file_name varchar2(100) := 'data.log'; db_dir_name varchar2(100) := 'LOADF'; dest_loc BLOB := empty_blob(); src_loc BFILE := BFILENAME(db_dir_name, file_name); destoff number := 1; srcoff number :=1; begin DBMS_LOB.OPEN(src_loc, DBMS_LOB.FILE_READONLY); DBMS_LOB.CREATETEMPORARY(lob_loc => dest_loc, cache => false); DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE); DBMS_LOB.LOADBLOBFROMFILE (dest_loc,src_loc,DBMS_LOB.getLength(src_loc),destoff,srcoff); insert into lobins values (dest_loc); commit; DBMS_LOB.CLOSE(dest_loc); DBMS_LOB.CLOSE(src_loc); end; /
Wednesday, September 28, 2016
Insert a File as BLOB Using DBMS_LOB
Following script could be used to insert a file as a BLOB into a table. The file to be inserted must reside in the location referred by the database directory LOADF. File name in this case data.log. File is inserted to table called lobins which only has single column which is of BLOB type.