Creating an External Table

Basic Syntax)

CREATE        TABLE    <table_name>     ( <col_name> <datatype>, …..) ORGANIZATION EXTERNAL     (TYPE <access_driver_type>      DEFAULT DIRECTORY <directory_name>      ACCESS PARAMETERS      ( … ) )      LOCATION ('<location_specifier>') ) REJECT LIMIT [0 | <number> | UNLIMITED];

 

ORGANIZATION EXTERNAL : The clause to create an external clause.

In fact, you will not create an actual table in the database, instead you will create metadata in the data dictionary which can be used to access to the external table.

The external files must already exist outside the database.

TYPE <access_driver_type> : indicates the access driver of the external table. ORACLE_LOADER is the default driver.

ACCESS PARAMETERS : Enables you to assign values to the parameters of the specific access for this external table.

LOCATION ('<location_specifier>') : Specifies one external locator for each external data source.

REJECT LIMIT : specifies how many errors will be allowed during a query. If the number of errors is over the limit, the Oracle server will abort the query.

 

Examples)

CREATE TABLE oldemp(     fname CHAR(25), lname CHAR(25))     ORGANIZATION EXTERNAL     (TYPE    ORACLE_LOADER     DEFAULT DIRECTORY emp_dir     ACCESS PARAMETERS     (RECORDS DELIMITED BY NEWLINE      NOBADFILE      NOLOGFILE     FIELDS    TERMINATED BY ','     (fname    POSITION(1:20) CHAR,      lname    POSITION(22:41) CHAR))     LOCATION ('emp.dat'))     PARALLEL 5     REJECT LIMIT 200;