How to map CLOB data from secondary file with SQL Loader

Question

I'm trying to load customer's data into table with CLOB, using SQL Loader. Because of the data volumes, the customer prefers to provide two files: primary - with "main" table data and secondary, with CLOBs.

I'm looking for correct layout of the input files and correct load syntax. Should secondary file contain an ID and how exactly will SQL Loader match records in two files?

The situation we have is approximately this (assuming that CLOBs are delimited by string '< lobend >'):

table emp with columns emp_id, name and resume (CLOB). Resume is optional, and sometimes it'll be null.

primary file

123, Jane
567, Mary
896, Bob

secondary file

Resume of Jane<lobend>
<lobend>
Resume of Bob<lobend>

BobC · Accepted Answer · 2017-02-22 16:43:41Z

Assume that your primary file is called primary.dat and your secondary file is called secondary.dat. Create a control file as follows:

load data
infile 'primary.dat'
into table persons
fields terminated by ','
(  emp_id char(3)
  ,ename char(10)
  ,resume lobfile( constant 'secondary.dat' ) terminated by "<lobend>\n"
)

Then load it:

sqlldr userid=scott/tiger control=loadlob.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Wed Feb 22 08:42:13 2017

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

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

Table PERSONS:
  3 Rows successfully loaded.

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

Check the data:

    EMP_ID ENAME      RESUME
---------- ---------- ------------------------------
       123  Jane      Resume of Jane
       456  Mary
       789  Bob       Resume of Bob

Collectives™ on Stack Overflow

How to map CLOB data from secondary file with SQL Loader

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related