Database Reference
In-Depth Information
( id ,owner ,object_name ,subobject_name ,object_id
,data_object_id ,object_type ,created ,last_ddl_time
,timestamp ,status ,temporary ,generated ,secondary
,namespace ,edition_name
)
We can convert this
easily
into an external table definition using SQL*Loader itself:
$ sqlldr eoda/foo big_table.ctl
external_table=generate_only
SQL*Loader: Release 12.1.0.1.0 - Production on Mon Feb 17 14:39:21 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
■
if you are curious about the SQllDr command and the options used with it, we'll be covering that in detail in
the next chapter.
Note
Notice the parameter
EXTERNAL_TABLE
passed to SQL*Loader. It causes SQL*Loader, in this case, to not load data,
but rather to generate a
CREATE TABLE
statement for us in the log file. This
CREATE TABLE
statement looked as follows
(this is an abridged form; I've edited out repetitive elements to make the example smaller):
CREATE TABLE "SYS_SQLLDR_X_EXT_BIG_TABLE"
(
"ID" NUMBER,
...
"EDITION_NAME" VARCHAR2(128)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00001':'big_table.bad'
LOGFILE 'SYS_SQLLDR_XT_TMPDIR_00001':'big_table.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY "|",
...
"EDITION_NAME" CHAR(255)
TERMINATED BY "|"
)
)
