Database Reference
In-Depth Information
Executing SQLLDR with the EXTERNAL_TABLE Parameter
One of the easiest ways to get started with external tables is to use an existing legacy control file to provide the
definition of the external table. As a first simple demonstration, a SQLLDR control file (complete details on
SQLLDR control files comes later in this chapter in the SQLLDR section) is used as follows:
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
First make sure a
DEPT
table exists:
EODA@ORA12CR1> create table dept as select * from scott.dept;
Table created.
Now the following SQLLDR command will generate the
CREATE TABLE
statement for our external table:
$ sqlldr eoda demo1.ctl external_table=generate_only
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Fri Mar 7 16:28:38 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Path used: External Table
The
EXTERNAL_TABLE
parameter has one of three values:
1.
NOT_USED
: This is self-evident in meaning and is the default value.
2.
EXECUTE
: This value means that SQLLDR will not generate a SQL
INSERT
statement and
execute it. Rather, it will create an external table and use a single bulk SQL statement to
load it.
3.
GENERATE_ONLY
: This value causes SQLLDR to not actually load any data, but only to
generate the SQL DDL and DML statements it would have executed into the log file it
creates.
■
DIRECT=TRUE
overrides
EXTERNAL_TABLE=GENERATE_ONLY
. if you specify
DIRECT=TRUE
, the data will be loaded
and no external table will be generated in oracle 10
g
and before. in oracle 11
g
release 1 and above you will receive
"
SQL*Loader-144: Conflicting load methods: direct=true/external_table=generate_only specified"
instead. not that you would think to do that, but just be aware these two options are incompatible.
Note
