Using External Tables (Walk-through with an example)

External tables are read-only tables whose metadata is stored in the database and actual data is stored outside the database. You can query the data in external table using SQL, PL/SQL and Java. Since these tables are read-only no DML operations are possible and no indexes can be created on them .

Syntax:
CREATE TABLE table_name ( col_name datatype, … )
ORGANIZATION EXTERNAL
(TYPE access_driver_type
DEFAULT DIRECTORY dir_name
ACCESS PARAMETERS
(… ) )
LOCATION ('loc_specifier') )
REJECT LIMIT [0 | number | UNLIMITED];


access_driver_type can be either ORACLE_LOADER or ORACLE_DATAPUMP. The access driver ensures that the data from data source is processed so that it matches the definition of external table. ORACLE_LOADER is default access driver type and is used for reading of data from external files whose format can be interpreted by SQL*Loader utility. ORACLE_DATAPUMP is used to both import and export data using a platform-independent format.

ACCESS PARAMETERS: optional clause that enables you to assign values to the parameters of specific access driver for this external table

LOCATION: specify the external data source file

REJECT_LIMIT: how many conversion queries can occur during a query of external data befors an Oracle error is returned and the query is aborted. Default is 0.

Walk-through on using external tables:

1) Assume that you have a flat file (test.dat) that has records as shown below:

1, vinay, NJ
2, suman, NY
3, ravi, KY

I have this file located at 'D:\Oracle\OCP' on my system.

2) Create directory object that will point to the directory location where the flat file exists. Use directory names when referring to external data sources, instead of hard-coding the OS path name for greater file management flexibility.

SQL> create directory test_dir as 'D:\Oracle\OCP';

Directory created.

3) Now create the table using organization as external
SQL> create table test (tno number(3), name varchar(15), loc varchar(10))
organization external
(type oracle_loader
default directory test_dir
access parameters
(records delimited by newline
nobadfile
nologfile
fields terminated by ',')
location ('test.dat'))
parallel 2
reject limit 100;

Table created.

4) SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY

5) If you try to perform DML operations on this table you will see the following error:
SQL> insert into test values (4,'sample', 'MO');
insert into test values (4,'sample', 'MO')
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table

6) You can add or delete records from your flat file on disk. I have added the following line to 'test.dat'

4, sai, MO

7) SQL> select * from test;

TNO NAME LOC
---------- --------------- ----------
1 vinay NJ
2 suman NY
3 ravi KY
4 sai MO

8) Once your external table is created, it can be queried like a relational table

NOTE: You can use CREATE TABLE AS SELECT statement to unload data into regular table in the database from an external table.

Ex: In the following example 'test' is an external table.
SQL> create table mytab
as select tno, name from test;

Table created.

SQL> select * from mytab;

TNO NAME
---------- ---------------
1 vinay
2 suman
3 ravi
4 sai
5 sample