Assume, you want to load an enormous amount of photos (image files) into an Oracle database, then SQL*Loader can do this easily for you. SQL*Loader is the tool to load the entire content of an operating system file into one LOB column. Image files, that you are loading with SQL*Loader are referred to as LOBFILEs. Usually you have a one-to-one correspondence between LOBFILEs and LOB columns.

Example

1. Create the LOB Table

The following table has one BLOB column to load the entire image file. You should store the LOB column(s) in a separate tablespace.

CREATE TABLE photo (
  empid              NUMBER(10)  NOT NULL,
  ename              VARCHAR2(20),
  job                VARCHAR2(20),
  photo              BLOB  DEFAULT empty_blob()
)
LOB (photo) STORE AS (
    TABLESPACE tab
    STORAGE (INITIAL 512K NEXT 512K PCTINCREASE 0)
    CHUNK 50
    PCTVERSION 10
    NOCACHE LOGGING
)
PCTFREE  10
PCTUSED  40
INITRANS 2
MAXTRANS 255
TABLESPACE tab
STORAGE (INITIAL     512K
         NEXT        512K
         MINEXTENTS  1
         MAXEXTENTS  UNLIMITED
         PCTINCREASE 0
         FREELISTS   1);

2. Create the Data File

Create a data file to drive the load. This data file must have one logical record per file to be loaded, and that logical record must specify the name of the file to be loaded into the LOB column.

"7369","SMITH","CLERK","2482.gif"
"7499","ALLEN","SALESMAN","2483.gif"
"7521","WARD","SALESMAN","2485.gif"
"7566","JONES","MANAGER","2486.gif"
"7654","MARTIN","SALESMAN","2487.gif"
"7698","BLAKE","MANAGER","2488.gif"
"7782","CLARK","MANAGER","2489.gif"
"7788","SCOTT","ANALYST","2490.gif"
"7839","KING","PRESIDENT","2491.gif"
"7844","TURNER","SALESMAN","2492.gif"
"7900","JAMES","CLERK","2493.gif"
"7902","FORD","ANALYST","2495.gif"
"7934","MILLER","CLERK","2496.gif"

3. Create SQL*Loader Controlfile

Define the column containing the file name as a FILLER field. Use the LOBFILE clause to load the contents of the file specified by the FILLER field into a LOB column. A filler field, specified by FILLER, is a datafile mapped field that does not correspond to a database column. Filler fields are assigned values from the data fields to which they are mapped.

LOAD DATA
    INFILE 'photo.dat'
    REPLACE INTO TABLE photo
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (
    empid        INTEGER  EXTERNAL,
    ename        CHAR,
    job          CHAR,
    photo_file   FILLER CHAR,
    photo        LOBFILE(photo_file) RAW TERMINATED BY EOF
    )

4. Load the Images

sqlldr scott/tiger@ARK2 control=photo.ctl
       log=photo.log data=photo.dat