Loading LOB data using SQL Loader

When there is LOB data at client side, SQL loader can be used to load into Oracle tables. Here are the steps to do it.

Create table with LOB column(s), if it is not already there.

create table tab1
( 
  id   number(5),
  text varchar2(10),
  dt   date,
  doc  clob
);

Here is the CSV file with data. Instead of LOB data, there will be respective file names that contain LOB data.

$ cat data.csv 
1,AAA,2014-05-01,doc1.txt
2,BBB,2014-05-02,doc2.txt

$ cat doc1.txt 
Text of doc1

$ cat doc2.txt 
Text of doc2

The control file of SQL Loader looks like below. There will be FILLER column for file names.

$ cat load.ctl 
LOAD DATA 
INFILE 'data.csv'
   APPEND INTO TABLE tab1
   FIELDS TERMINATED BY ','
   (
    id        CHAR(5),
    text      CHAR(10),
    dt        DATE "YYYY-MM-DD" ":dt",
    file_name FILLER CHAR(100),
    doc       LOBFILE(file_name) TERMINATED BY EOF
    )

The output on screen looks like below when sqlldr command is executed.

$ sqlldr userid=murty/xxxx@mm1 control=load.ctl 

SQL*Loader: Release 12.1.0.1.0 - Production on Tue May 13 10:13:26 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2

Table TAB1:
  2 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.

We can verify if the data is loaded successfully

SQL> select * from tab1;

 ID TEXT       DT
---------- ---------- ---------
DOC
--------------------------------------------------------------------------------
  1 AAA       01-MAY-14
Text of doc1

  2 BBB       02-MAY-14
Text of doc2

The same procedure can be used for BLOB data also.