External Table

Create Internal Representation of the External Table   

CREATE TABLE (

)

ORGANIZATION EXTERNAL

(TYPE oracle_loader

DEFAULT DIRECTORY

ACCESS PARAMETERS (

RECORDS DELIMITED BY newline

BADFILE

DISCARDFILE

LOGFILE

[READSIZE ]

[SKIP

FIELDS TERMINATED BY ''

REJECT ROWS WITH ALL NULL FIELDS

MISSING FIELD VALUES ARE NULL

())\

LOCATION (''))

[PARALLEL]

REJECT LIMIT ;

conn uwclass/uwclass

CREATE TABLE ext_tab (

empno  CHAR(4),

ename  CHAR(20),

job    CHAR(20),

deptno CHAR(2))

ORGANIZATION EXTERNAL (

  TYPE oracle_loader

  DEFAULT DIRECTORY ext

    ACCESS PARAMETERS (

    RECORDS DELIMITED BY NEWLINE

    BADFILE 'bad_%a_%p.bad'

    LOGFILE 'log_%a_%p.log'

    FIELDS TERMINATED BY ','

    MISSING FIELD VALUES ARE NULL

    REJECT ROWS WITH ALL NULL FIELDS

    (empno, ename, job, deptno))

    LOCATION ('demo1.dat')

  )

PARALLEL

REJECT LIMIT 0

NOMONITORING;

SELECT * FROM ext_tab;

SELECT table_name FROM user_tables;

desc user_external_tables

col table_name format a15

col type_name format a15

col reject_limit format a15

SELECT table_name, type_name, default_directory_name, reject_limit, access_type FROM user_external_tables;

DROP TABLE ext_tab PURGE;

CREATE TABLE ext_tab (

empno CHAR(4),

ename CHAR(20),

job CHAR(20),

deptno CHAR(2))

ORGANIZATION EXTERNAL

(TYPE oracle_loader

DEFAULT DIRECTORY ext

ACCESS PARAMETERS

(FIELDS TERMINATED BY ','

MISSING FIELD VALUES ARE NULL

(empno, ename, job, deptno))

LOCATION ('demo1.dat','demo2.dat'))

PARALLEL

REJECT LIMIT 0;

SELECT * FROM ext_tab;

External Table For Writing and Reading          

CREATE TABLE (

)

ORGANIZATION EXTERNAL

(TYPE oracle_datapump

DEFAULT DIRECTORY

LOCATION ('

[PARALLEL]

AS

;

CREATE TABLE ext_write (

tab_name, tblspname, numblocks)

ORGANIZATION EXTERNAL

(TYPE oracle_datapump

DEFAULT DIRECTORY ext

LOCATION ('table_history.exp'))

PARALLEL

AS

SELECT table_name, tablespace_name, blocks

FROM user_tables;

SELECT *

FROM ext_write;

SELECT *

FROM ext_write

WHERE numblocks > 100;

SELECT table_name, type_name, default_directory_name,

reject_limit, access_type

FROM user_external_tables;

-- open ext_write_####_####.log files

-- open c:\external able_history.exp

DROP TABLE ext_write;