How to Create External Table in Oracle 10g Database

External Table store the data on external file system outside the DB as standard tables store their data in a tablespace managed by the RDBMS.

External Table Script:

CREATE TABLE WMS_PHONEBOOK

(

  NAME       VARCHAR2(50 BYTE),

  SURNAME    VARCHAR2(50 BYTE),

  PHONE      NUMBER,

  BIRTHDATE  DATE,

  NOTES      VARCHAR2(200 BYTE)

)

ORGANIZATION EXTERNAL

  (  TYPE ORACLE_LOADER

     DEFAULT DIRECTORY UPLOAD_DIR -- Oracle Directory

     ACCESS PARAMETERS 

       ( records delimited by newline

        fields terminated by ','

        missing field values are NULL

        reject rows with all null fields

        (

            name,

            surname,

            phone,

            birthdate char(22) date_format date mask "dd.mm.yyyy",

            notes

        )

     )

     LOCATION (UPLOAD_DIR:'PHONEBOOK.txt')

  )

REJECT LIMIT UNLIMITED

NOPARALLEL

NOMONITORING