Databases Reference
In-Depth Information
The create table command syntax is just like any other regular table creation (
A
), (
B
),
up to the point where the
ORGANIZATION EXTERNAL
(
C
) keyword appears,
this is the point where the actual External Table definition starts. In this case the
External Table is accessed by the
ORACLE_LOADER
driver (
D
). Next, the external
flat file is defined, and here it is declared the Oracle
DIRECTORY
(
E
) where the flat
file resides. The
ACCESS PARAMETERS (F
) section specifies how to access the flat
file and it declares whether the file is a fixed or variable size record, and which other
SQL*Loader loading options are declared. The
LOCATION
(
H
) keyword defines the
name of the external data file. It must be pointed out that as this is an External Table
managed by the
SQL_LOADER
driver the
ACCESS_PARAMETERS
section must
be defined, in the case of External Tables based on the
DATAPUMP_DRIVER
this
section is not required.
The columns are defined only by name (
G
), not by type. This is permitted from the
SQL*Loader perspective, and allows for dynamic column definition. This column
schema definition is more flexible, but it has a drawbackâdata formats such as those
in
DATE
columns must match the database date format, otherwise the row will be
rejected. There are ways to define the date format working around this requirement.
Assuming the date column changes from its original default format mask
"DD-MON-RR" to "DD-MM-RR", then the column definition must change from a
simple
CHAR
column to a
DATE
with format mask column definition.
Original format:
"HIRE_DATE" CHAR(255)
Changed format:
"HIRE_DATE" DATE "DD-MM-RR"
When working with an External Table, the access parameter is
not validated at creation time, so if there are malformed rows,
or if there are improperly defined access parameters, an error is
shown, similar to the one below.
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
Once the data is created and all required OS privileges have been properly validated,
the data can be seen from inside the database, just as if it were a regular Oracle table.
Search WWH ::
Custom Search