Database Reference
In-Depth Information
When using
GENERATE_ONLY
, we can see the following in the
demo1.log
file:
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/tkyte'
We may or may not see a
CREATE DIRECTORY
statement in the log file. SQLLDR connects to the database during
the external table script generation and queries the data dictionary to see if a suitable directory already exists. In this
case, there was no suitable directory in place, so SQLLDR generated a
CREATE DIRECTORY
statement for us. Next, it
generated the
CREATE TABLE
statement for our external table:
CREATE TABLE statement for external table:
------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
)
SQLLDR had logged into the database; that is how it knows the exact datatypes to be used in this external
table definition (e.g., that
DEPTNO
is a
NUMBER(2)
). It picked them up right from the data dictionary. Next, we see the
beginning of the external table definition:
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
The
ORGANIZATION EXTERNAL
clause tells Oracle this is not a “normal” table. We saw this clause before in
Chapter 10 when we looked at IOTs. Currently there are three organization types:
HEAP
for a normal table,
INDEX
for
an IOT, and
EXTERNAL
for an external table. The rest of the text starts to tell Oracle more about the external table.
The
ORACLE_LOADER
type is one of two supported types (in Oracle9
i
it is the
only
supported type). The other type is
ORACLE_DATAPUMP
, the proprietary Data Pump format used by Oracle in Oracle 10
g
and later. We will take a look at
that type in a subsequent section on data unloading—it is a format that can be used to both load and unload data. An
external table may be used both to create a Data Pump format file and to subsequently read it.
The very next section we encounter is the
ACCESS PARAMETERS
section of the external table. Here we describe to
the database how to process the input file. As you look at this, you should notice the similarity to a SQLLDR control
file; this is no accident. For the most part, SQLLDR and external tables use very similar syntax:
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad'
LOGFILE 'demo1.log_xt'
READSIZE 1048576
SKIP 6
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
