8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Import BLOB

Related articles.

The following article presents a simple methods for importing a file into a BLOB datatype. First a directory object is created to point to the relevant filesystem directory.

CREATE OR REPLACE DIRECTORY BLOB_DIR AS 'C:\';

Next we create a table to hold the BLOB.

CREATE TABLE tab1 (
  id        NUMBER,
  blob_data BLOB
);

We import the file into a BLOB datatype and insert it into the table.

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.