Import BLOB Contents

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 images 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;
BEGIN
  INSERT INTO tab1 (id, clob_data)
  VALUES (1, empty_blob())
  RETURN clob_data INTO l_blob;

  l_bfile := BFILENAME('IMAGES', 'MyImage.gif');
  DBMS_LOB.fileopen(l_bfile, Dbms_Lob.File_Readonly);
  DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  COMMIT;
END;
/

To update an existing BLOB do the following.

DECLARE
  l_bfile  BFILE;
  l_blob   BLOB;
BEGIN
  SELECT clob_data
  INTO   l_clob
  FROM   tab1
  WHERE  id = 1
  FOR UPDATE;

  l_bfile := BFILENAME('IMAGES', 'MyImage.gif');
  DBMS_LOB.fileopen(l_bfile, Dbms_Lob.File_Readonly);
  DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.