Creating an Oracle External Table

Prerequisite: Creating an Oracle Directory and grant access for this directory to the Owner of External Table


Creating the directory

CREATE OR REPLACE DIRECTORY my_dir AS '/u01/extab_dir';

Granting access for this directory to Owner of external table

GRANT READ, WRITE ON DIRECTORY my_dir TO extab_owner;

Sample external table create statement for unix file with blank as column delimiter

CREATE TABLE my_table_ext
(
      field0  VARCHAR2(2000)
    , field1  VARCHAR2(2000)
    , field2  VARCHAR2(2000)
    , field3  VARCHAR2(2000)
    , field4  VARCHAR2(2000)
    , field5  VARCHAR2(2000)
    , field6  VARCHAR2(2000)
    , field7  VARCHAR2(2000)
    , field8  VARCHAR2(2000)
    , field9  VARCHAR2(2000)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY my_dir
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY newline
        BADFILE my_dir:'my_table.bad'
        DISCARDFILE my_dir:'amy_table.dsc'
        LOGFILE my_dir:'my_table.log'
        CHARACTERSET AL32UTF8
        SKIP 0 FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"'
        LRTRIM
        MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS
    )
        LOCATION (my_dir:'my_table.txt')
)
REJECT LIMIT 0;