Database Reference
In-Depth Information
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
COMMENTS char(1000000)
)
■
this example is from UniX/Linux, where the end-of-line marker is 1 byte, hence the
STR
setting in the
preceding control file. on Windows, it would have to be
'7C0D0A'
.
Note
To load the data file, we specify
CHAR(1000000)
on column
COMMENTS
since SQLLDR defaults to
CHAR(255)
for any
input field as discussed previously. The
CHAR(1000000)
will allow SQLLDR to handle up to 1,000,000 bytes of input
text. You must set this to a value that is larger than any expected chunk of text in the input file. Reviewing the loaded
data, we see the following:
EODA@ORA12CR1> select comments from dept;
COMMENTS
-------------------------------------------------------------------------------
This is the Consulting
Office in 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 or until we hit the magic end of record marker,
the | followed by an end of line - it is right here ->
This is the Sales
Office in Virginia
This is the Accounting
Office in Virginia
The one thing to observe here is that the doubled-up quotes are no longer doubled up. SQLLDR removed the
extra quotes we placed there.
Loading LOB Data That Is Out of Line
A common scenario is to have a data file that contains the names of files to load into the LOBs, instead of having the
LOB data mixed in with the structured data. This offers a greater degree of flexibility, as the data file given to SQLLDR
does not have to use one of the four methods to get around having embedded newlines in the input data, as would
frequently happen with large amounts of text or binary data. SQLLDR calls this type of additional data file a
LOBFILE
.
SQLLDR can also support the loading of a structured data file that points to another, single data file. We can tell
SQLLDR how to parse LOB data from this other file, so that each row in the structured data gets loaded with a piece
of it. I find this mode to be of limited use (in fact, I've never found a use for it to date), and I will not discuss it here.
SQLLDR refers to these externally referenced files as
complex secondary data files
.
