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.

Setup
DML Approach
DBMS_LOB Approach

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:

Hope this helps. Regards Tim...

Back to the Top.