External Tables - Pro Oracle Database 12c Administration - page 387

Creating an External Table
Then, fashion the script that creates the external table that will reference the flat file. The CREATE TABLE...
ORGANIZATION EXTERNAL statement provides the database with the following information:

• How to interpret data in the flat file and a mapping of data in file to column definitions in the

database

DEFAULT DIRECTORY clause that identifies the directory object, which in turn specifies the
directory of the flat file on disk

• A

LOCATION clause, which identifies the name of the flat file

• The

The next statement creates a database object that looks like a table but that is able to retrieve data directly from
the flat file:

create table exadata_et(
exa_id NUMBER
,machine_count NUMBER
,hide_flag NUMBER
,oracle NUMBER
,ship_date DATE
,rack_type VARCHAR2(32)
)
organization external (
type oracle_loader
default directory exa_dir
access parameters
(
records delimited by newline
fields terminated by '|'
missing field values are null
(exa_id
,machine_count
,hide_flag
,oracle
,ship_date char date_format date mask "mm/dd/yyyy"
,rack_type)
)
location ('ex.csv')
)
reject limit unlimited;

An external table named EXADATA_ET is created when you execute this script. Now, use SQL*Plus to view the
contents of the flat file:

SQL> select * from exadata_et;

EXA_ID MACHINE_COUNT HIDE_FLAG ORACLE SHIP_DATE RACK_TYPE
---------- ------------- ---------- ---------- ---------- --------------------
5 2 0 0 04-DEC-11 Half
6 1 0 1 06-SEP-12 Quarter
7 4 0 1 10-AUG-12 Full
8 1 1 0 15-JUN-12 Quarter