Oracle

Creating an External Table

Figo Kim 2006. 12. 7. 16:45
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

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;

'Oracle' 카테고리의 다른 글

Use of Variables  (0) 2006.12.31
Creating an External Table  (0) 2006.12.07
External Tables [1]  (0) 2006.12.07
External Tables [1]  (0) 2006.12.07
The secret about her  (0) 2006.12.05