8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Import BLOB
A simple method for importing the contents of a file into a BLOB datatype.
Related articles.
- Export BLOB Contents (8i)
- Export BLOB Contents Using UTL_FILE
- Import CLOB Contents
- Export CLOB Contents
Setup
A directory object is created to point to the relevant filesystem directory.
create or replace directory blob_dir as 'C:\';
We create a table to hold the BLOB.
create table tab1 ( id number, blob_data blob );
DML Approach
We can import the BLOB with a single DML statement. This would be the preferred approach.
insert into tab1 (id, blob_data) values (1, to_blob(bfilename('BLOB_DIR', 'MyImage.gif')));
To update an existing blob we would do the following.
update tab1 set blob_data = bfilename('BLOB_DIR', 'MyImage.gif') where id = 1;
This DML approach was not available when this article was first written.
DBMS_LOB Approach
We insert a row with an empty BLOB, then import the file into the BLOB.
declare l_bfile bfile; l_blob blob; l_dest_offset integer := 1; l_src_offset integer := 1; begin insert into tab1 (id, blob_data) values (1, empty_blob()) return blob_data into l_blob; l_bfile := bfilename('BLOB_DIR', 'MyImage.gif'); dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly); -- loadfromfile deprecated. -- dbms_lob.loadfromfile(l_blob, l_bfile, dbms_lob.getlength(l_bfile)); dbms_lob.loadblobfromfile ( dest_lob => l_blob, src_bfile => l_bfile, amount => dbms_lob.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset); dbms_lob.fileclose(l_bfile); commit; end; /
To update an existing BLOB do the following.
declare l_bfile bfile; l_blob blob; l_dest_offset integer := 1; l_src_offset integer := 1; begin select blob_data into l_blob from tab1 where id = 1 for update; l_bfile := bfilename('BLOB_DIR', 'MyImage.gif'); dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly); dbms_lob.trim(l_blob, 0); -- loadfromfile deprecated. -- dbms_lob.loadfromfile(l_blob, l_bfile, dbms_lob.getlength(l_bfile)); dbms_lob.loadblobfromfile ( dest_lob => l_blob, src_bfile => l_bfile, amount => dbms_lob.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset); dbms_lob.fileclose(l_bfile); end; /
In reality you would probably put this code into a stored procedure, or packaged procedure. This example is available here.
create or replace procedure file_to_blob (p_blob in out nocopy blob, p_dir in varchar2, p_filename in varchar2) as l_bfile bfile; l_dest_offset integer := 1; l_src_offset integer := 1; begin l_bfile := bfilename(p_dir, p_filename); dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly); dbms_lob.trim(p_blob, 0); if dbms_lob.getlength(l_bfile) > 0 then dbms_lob.loadblobfromfile ( dest_lob => p_blob, src_bfile => l_bfile, amount => dbms_lob.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset); end if; dbms_lob.fileclose(l_bfile); end file_to_blob; /
You might call this like the following.
declare l_blob blob; begin select blob_data into l_blob from tab1 where id = 1 for update; file_to_blob (p_blob => l_blob, p_dir => 'BLOB_DIR', p_filename => 'MyImage.gif'); end; /
For more information see:
- Export BLOB Contents (8i)
- Export BLOB Contents Using UTL_FILE
- Import CLOB Contents
- Export CLOB Contents
- DBMS_LOB
- UTL_FILE
Hope this helps. Regards Tim...