External Tables

External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.

Limitations on external tables

Because external tables are new, Oracle has not yet perfected their use. In Oracle9i the feature has several limitations, including:
• No support for DML. External tables are read-only, but the base data can be edited in any text editor.
• Poor response for high-volume queries. External tables have a processing overhead and are not suitable for large tables.
Example: The example below describes how to create external files, create external tables, query external tables and create views.
Step I: Creating the flat files, which will be queried
The file "emp_ext1.dat" contains the following sample data:
101,Andy,FINANCE,15-DEC-1995
102,Jack,HRD,01-MAY-1996
103,Rob,DEVELOPMENT,01-JUN-1996
104,Joe,DEVELOPMENT,01-JUN-1996
The file "emp_ext2.dat" contains the following sample data:
105,Maggie,FINANCE,15-DEC-1997
106,Russell,HRD,01-MAY-1998
107,Katie,DEVELOPMENT,01-JUN-1998
108,Jay,DEVELOPMENT,01-JUN-1998

Copy these files under "C:\EXT_TABLES"
Step II: Create a Directory Object where the flat files will reside
SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\EXT_TABLES';

Directory created.
Step III: Create metadata for the external table
SQL> CREATE TABLE emp_ext
(
empcode NUMBER(4),
empname VARCHAR2(25),
deptname VARCHAR2(25),
hiredate date
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tables
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION ('emp_ext1.dat','emp_ext2.dat')
)
REJECT LIMIT UNLIMITED;

Table created.

The REJECT LIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data.
Step V: Creating Views
SQL> CREATE VIEW v_empext_dev AS
SELECT * FROM emp_ext
WHERE deptname = 'DEVELOPMENT';
View created.

Dropping External Tables
For an external table, the DROP TABLE statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.