External Tables

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.