Data Loading and Unloading - Expert Oracle Database Architecture - page 734

It is interesting to note that you can write your own functions to be called from SQLLDR. This is a straightforward
application of the fact that PL/SQL can be called from SQL.

How Do I Load Data with Embedded Newlines?
This is something that has been problematic for SQLLDR historically: how to load free-form data that may include
a newline in it. The newline character is the default end-of-line character to SQLLDR, and the ways around this did
not offer much flexibility in the past. Fortunately, in Oracle 8.1.6 and later versions we have some new options. The
options for loading data with embedded newlines are now as follows:

• Load the data with some other character in the data that represents a newline (e.g., put the

string \n in the text where a newline should appear) and use a SQL function to replace that
text with a CHR(10) during load time.
Use the

•

FIX attribute on the INFILE directive, and load a fixed-length flat file. In this case there
is no record terminator; rather, the fact that each record is exactly as long as every other record
is used to determine where records begin and end.

• Use the

VAR attribute on the INFILE directive, and load a variable-width file that uses a format
such that the first few bytes of each line specify the length in bytes of the line to follow.

• Use the

STR attribute on the INFILE directive to load a variable-width file with some sequence
of characters that represents the end of line, as opposed to just the newline character
representing this.

The following sections demonstrate each in turn.

Use a Character Other Than a Newline
This is an easy method if you have control over how the input data is produced. If it is easy enough to convert the
data when creating the data file, this will work fine. The idea is to apply a SQL function to the data on the way into the
database, replacing some string of characters with a newline. Let's add another column to our DEPT table:

EODA@ORA12CR1> alter table dept add comments varchar2(4000);
Table altered.

We'll use this column to load text into. An example control file with inline data could be as follows:

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
COMMENTS "replace(:comments,'\\n',chr(10))"
)
BEGINDATA
10,Sales,Virginia,This is the Sales\nOffice in Virginia
20,Accounting,Virginia,This is the Accounting\nOffice in Virginia
30,Consulting,Virginia,This is the Consulting\nOffice in Virginia
40,Finance,Virginia,This is the Finance\nOffice in Virginia