Load LOB Using Oracle SQL*Loader

You can use the Oracle SQL*Loader utility to import the data from files (containing images i.e.) into an Oracle table.

For example, let's consider a sample table:

  CREATE TABLE pictures 
  (
     id     NUMBER(5),
     data BLOB
  );

You are given a set of image files (.png files i.e.) and want to load them into this table.

You need to create a data file (pictures_data.txt i.e.) containing the list of file paths as well as the values for other columns in the table, for example, ID column:

  1,C:\data\image1.png
  2,C:\data\image2.png
  3,C:\data\image3.png

Then you have to create a control file ((pictures_data.ctl i.e.) for Oracle SQL*Loader as follows:

  LOAD DATA
  INFILE 'C:\data\pictures_data.txt'
  INTO TABLE pictures
  FIELDS TERMINATED BY ','
  (
    id     INTEGER EXTERNAL,
    d      FILLER,
    data LOBFILE(d) TERMINATED BY EOF
  )

This control files defines the data file location (pictures_data.txt) and uses the FILLER and LOBFILE clauses to specify that the data file contains references to BLOB values (image files) rather than its data itself.

Now everything is ready to run the SQL*Loader utility as follows:

  sqlldr userid=SCOTT/TIGGER control=C:\data\pictures_data.ctl

You need to specify your credentials as well as the path to the control file.