PL/SQL File to Blob

Share Button


Converting physical files into binary data type is an important requirement. In this tutorial, I am going to explain converting files into blob data type.

Basically, Oracle has two large object data types.

  • BLOB (Binary Large Object): Used for keeping images, binary documents such as word, excel, pdf..
  • CLOB (Character Large Object): Used for keeping huge strings where varchar2 is not enough.

BFILE is also a data type which points or locates physical file.

Follow these steps in order to convert file to blob data type.

  1. Create database directory which is set as the physical file’s path.
  2. Define BLOB and BFILE variables.
  3. Insert empty blob into table.
  4. Return blob data type into blob variable.
  5. Open blob and bfile.
  6. Load bfile into blob by using dbms_lob package.
  7.  Close all lob objects.
  8. Commit.

Create table with following script:

Create database directory object. This object is the equivalent of the physical file in machine. Please pay attention that the path you define is physically exist.

Declare BLOB and BFILE variables:

Insert empty blob into table and table column into blob variable:

Open lobs in read and read-write modes.

Load binary data into blob variable from bfile by using dbms_lob.loadfromfile.

Close the lobs and commit the transaction.

All script: