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 '/erpesed1/erpapp/appl/geps/gepspo/1.0.0/in'

Step II: Create a Directory Object where the flat files will reside

SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS '/erpesed1/erpapp/appl/geps/gepspo/1.0.0/in'';

Directory created.

Grant read and write permissions to the directory:

GRANT READ,WRITE ON DIRECTORY EXT_TABLES TO PUBLIC;

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 IV: Creating Views

SQL> CREATE VIEW v_empext_dev AS

                 SELECT * FROM emp_ext

                 WHERE deptname = 'DEVELOPMENT';

View created.

Step V: View data

SELECT * FROM emp_ext

SELECT * FROM v_empext_dev

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

Step VI: Drop Table

drop table emp_ext