Database Reference
In-Depth Information
Loading LOB Data via SQLLDR
We will now investigate how to load data into a LOB via SQLLDR. There is more than one method for doing this, but
we will investigate the two most common:
•
When the data is inline with the rest of the data.
•
When the data is stored out of line, and the input data contains a file name to be loaded with
the row. These are also known as
secondary data files
(
SDFs
) in SQLLDR terminology.
We will start with data that is inline.
Loading LOB Data That Is Inline
These LOBs will typically have newlines and other special characters embedded in them. Therefore, you will almost
always use one of the four methods detailed in the “How Do I Load Data with Embedded Newlines?” section to load
this data. Let's begin by modifying the
DEPT
table to have a
CLOB
instead of a big
VARCHAR2
field for the
COMMENTS
column:
EODA@ORA12CR1> truncate table dept;
Table truncated.
EODA@ORA12CR1> alter table dept drop column comments;
Table altered.
EODA@ORA12CR1> alter table dept add comments clob;
Table altered.
For example, say we have a data file (
demo.dat
) that has the following contents:
10, Sales,Virginia,This is the Sales
Office in Virginia|
20,Accounting,Virginia,This is the Accounting
Office in Virginia|
30,Consulting,Virginia,This is the Consulting
Office in Virginia|
40,Finance,Virginia,"This is the Finance
Office in Virginia, it has embedded commas and is
much longer than the other comments field. If you
feel the need to add double quoted text in here like
this: ""You will need to double up those quotes!"" to
preserve them in the string. This field keeps going for up to
1000000 bytes (because of the control file definition I used)
or until we hit the magic end of record marker,
the | followed by an end of line - it is right here ->"|
Each record ends with a pipe symbol (
|
), followed by the end-of-line marker. The text for department 40 is much
longer than the rest, with many newlines, embedded quotes, and commas. Given this data file, we can create a control
file such as this:
LOAD DATA
INFILE demo.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
