Database Reference
In-Depth Information
(
"DEPTNO" CHAR(255)
TERMINATED BY ",",
"DNAME" CHAR(255)
TERMINATED BY ",",
"LOC" CHAR(255)
TERMINATED BY ","
)
)
These access parameters show how to set up an external table so that it processes files pretty much identically to
the way SQLLDR would:
•
RECORDS
: Records are terminated by newlines by default, as they are for SQLLDR.
•
BADFILE
: There is a bad file (a file where records that fail processing are recorded to) set up in
the directory we just created.
•
LOGFILE
: There is a log file that is equivalent to SQLLDR's log file set up in the current working
directory.
•
READSIZE
: This is the default buffer used by Oracle to read the input data file. It is 1MB in this
case. This memory comes from the PGA in dedicated server mode and the SGA in shared
server mode, and it is used to buffer the information in the input data file for a session (refer to
Chapter 4, where we discussed PGA and SGA memory). Keep that shared server fact in mind if
you're using shared servers: the memory is allocated from the SGA.
•
SKIP 6
: This determines how many records in the input file should be skipped. You might be
asking, “Why 'skip 6'?” Well, we used
INFILE *
in this example;
SKIP 6
is used to skip over the
control file itself to get to the embedded data. If we did not use
INFILE *
, there would be no
SKIP
clause at all.
•
FIELDS TERMINATED BY
: This is just as we used in the control file itself. However, the external
table did add
LDRTRIM
, which stands for
LoaDeR TRIM
. This is a trim mode that emulates the
way in which SQLLDR trims data by default. Other options include
LRTRIM
,
LTRIM
, and
RTRIM
(for left/right trimming of whitespace); and
NOTRIM
to preserve all leading/trailing whitespace.
•
REJECT ROWS WITH ALL NULL FIELDS
: This causes the external table to log to the bad file any
entirely blank lines and to not load that row.
•
The column definitions themselves
: This is the metadata about the expected input data values.
They are all character strings in the data file to be loaded, and they can be up to 255 characters
in length (SQLLDR's default size), and terminated by
,
and optionally enclosed by quotes.
■
For a comprehensive list of all options available to you when using external tables, review the
Oracle Utilities
Guide
. this reference contains a section dedicated to external tables. the
Oracle SQL Language Reference Guide
provides
the basic syntax, but not the details of the
ACCESS PARAMETERS
section.
Note
