External Tables in Oracle 9i

Ajay Gursahani

This
article gives a brief understanding about External tables. External Tables are
defined as tables that do not reside in the database, and can be in any format
for which an access driver is provided. This external table definition can be
thought of as a view that allows running any SQL query against external data
without requiring that the external data first be loaded into the database.

You
can, for example, select, join, or sort external table data. You can also
create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or
DELETE)
are possible, and indexes cannot be created on external tables.

Oracle
provides the means of defining the metadata for external tables through the CREATE TABLE …
ORGANIZATION EXTERNAL statement.

Before firing the above command
we need to create a directory object where the external files will reside.

CREATE OR REPLACE DIRECTORY EXT_TABLES AS ‘C:EXT_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.

“The ORACLE_LOADER is an access driver for loading
data from the external files into the tables.”

Step IV: Querying Data

SQL> SELECT * FROM  emp_ext;
        EMPCODE	 EMPNAME              DEPTNAME               HIREDATE
       ——— ——————- ———————- ———
         101 Andy                       FINANCE                15-DEC-95
         102 Jack                       HRD                    01-MAY-96
         103 Rob                        DEVELOPMENT            01-JUN-96
         104 Joe                        DEVELOPMENT            01-JUN-96
         105 Maggie                     FINANCE                15-DEC-97
         106 Russell                    HRD                    01-MAY-98
         107 Katie                      DEVELOPMENT            01-JUN-98
         108 Jay                        DEVELOPMENT            01-JUN-98
8 rows selected.

Step V: Creating Views

SQL> CREATE VIEW v_empext_dev AS
		SELECT * FROM emp_ext
		WHERE deptname=’DEVELOPMENT’;
View created.
SQL> SELECT * FROM v_empext_dev;
         EMPCODE EMPNAME            DEPTNAME             HIREDATE
       ———— ————- ———————- ———
         103 Rob                     DEVELOPMENT       01-JUN-96
         104 Joe                     DEVELOPMENT       01-JUN-96
         107 Katie                   DEVELOPMENT       01-JUN-98
         108 Jay                     DEVELOPMENT       01-JUN-98

You
can get the information of the objects you have created through DBA_OBJECTS, ALL_OBJECTS
or USER_OBJECTS.

SQL> 	SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE ‘EMP_EXT’;
OBJECT_NAME                 OBJECT_TYPE
———————- 	——————
EMP_EXT		          TABLE
1 row selected.
SQL> 	SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE ‘EXT_TABLES’;
OBJECT_NAME                 OBJECT_TYPE
———————- 	——————
EXT_TABLES                  DIRECTORY
1 row selected.

Populating Tables using the INSERT command

You can populate data from external files using an
"insert into … select from" statement instead of using SQL*Loader. This
method provides very fast data loads.

Example:

Consider a table EMPLOYEES:

SQL> desc EMPLOYEES;
 Name                              Null?    	Type
 ——————————— ——– 	————–
 EMPCODE                                       NUMBER(4)
 EMPNAME                                       VARCHAR2(25)
 DEPTNAME                                      VARCHAR2(25)
 HIREDATE                                      DATE
SQL> INSERT INTO employees
     (empcode,empname,deptname,hiredate) SELECT * FROM emp_ext;
8 rows created.
SQL> SELECT * FROM employees;
        EMPCODE 	EMPNAME               DEPTNAME               HIREDATE
       ———— ——————- ———————- ———
         101 Andy                        FINANCE                 15-DEC-95
         102 Jack                        HRD                     01-MAY-96
         103 Rob                         DEVELOPMENT             01-JUN-96
         104 Joe                         DEVELOPMENT             01-JUN-96
         105 Maggie                      FINANCE                 15-DEC-97
         106 Russell                     HRD                     01-MAY-98
         107 Katie                       DEVELOPMENT             01-JUN-98
         108 Jay                         DEVELOPMENT             01-JUN-98
8 rows selected.

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.

Summary

The external files are thus tables in the data dictionary,
which can be queried as you would query ordinary Oracle tables. You can perform
fast data loads using the above method instead of using SQL*Loader.