Database Reference
In-Depth Information
Notice how in the call to replace we had to use
\\n
, not just
\n
. This is because
\n
is recognized by SQLLDR as a
newline, and SQLLDR would have converted it into a newline, not a two-character string. When we execute SQLLDR
with the preceding control file, the table
DEPT
is loaded with the following:
EODA@ORA12CR1> select deptno, dname, comments from dept;
DEPTNO DNAME COMMENTS
---------- -------------- ------------------------------
10 SALES This is the Sales
Office in Virginia
20 ACCOUNTING This is the Accounting
Office in Virginia
30 CONSULTING This is the Consulting
Office in Virginia
40 FINANCE This is the Finance
Office in Virginia
Use the FIX Attribute
The
FIX
attribute is another method available to us. If we use this, the input data must appear in fixed-length records.
Each record will be exactly the same number of bytes as any other record in the input data set. When using
positional
data, the use of the
FIX
attribute is especially valid. These files are typically fixed-length input files to begin with.
When using free-form delimited data, it is less likely that we will have a fixed-length file, as these files are generally of
varying length (this is the entire point of delimited files: to make each line only as big as it needs to be).
When using the
FIX
attribute, we must use an
INFILE
clause, as this is an option to the
INFILE
clause.
Additionally, the data must be stored externally, not in the control file itself, using this option. So, assuming we have
fixed-length input records, we can use a control file such as this:
LOAD DATA
INFILE demo.dat "fix 80"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
COMMENTS
)
This file specifies an input data file that will have records that are 80 bytes each.
This includes the trailing newline
that may or may not be there. In this case, the newline is nothing special in the input data file. It is just another
character to be loaded or not. This is the thing to understand: the newline at the end of the record (if present) will
become part of the record. To fully understand this, we need a utility to dump the contents of a file on the screen so
we can see what is really in there. Using any UNIX/Linux variant, this is pretty easy to do with
od
, a program to dump
files to the screen in octal and other formats. We'll use the following
demo.dat
file. Note that the first column in the
