Oracle9i New Feature Series: External Tables

External Table introduced in Oracle9i is and excellent feature for ETL operations, allows for flat files that reside outside the database, to be accessed just like relational tables within the database. The flat-file data can be queried and joined to other tables using standard SQL. From a user’s point of view, the main difference between an external table and a regular table is that the external table is read-only. The metadata is stored in the Oracle database, where as the actual data resides outside the database.

You create the external table using the ORAGANIZATION EXTERNAL clause of the CREATE TABLE.

To create an external table, you must create a DIRECTORY in Oracle and have the READ object privilege on the directory in which the external data resides. Also, no constraints are permitted on external tables.

CREATE DIRECTORY "UNIX_FLATFILE" AS '/FileImport/Externall';

 

GRANT READ ON DIRECTORY "UNIX_FLATFILE" TO "MYUSER";

 

CREATE TABLE MYUSER.TEST_EXT_TAB

(

  ASS_ID     NUMBER,

  TID_ID     NUMBER,

  ASS_DATE    VARCHAR2(10 BYTE),

  MCFD        VARCHAR2(5 BYTE),

  HRS         VARCHAR2(5 BYTE),

  PRES        VARCHAR2(6 BYTE),

  PIPELINE    VARCHAR2(30 BYTE),

  DAILY_DATE  VARCHAR2(18 BYTE)

)

ORGANIZATION EXTERNAL

  (  TYPE ORACLE_LOADER

     DEFAULT DIRECTORY UNIX_FLATFILE

     ACCESS PARAMETERS

       ( RECORDS DELIMITED BY NEWLINE

             CHARACTERSET WE8MSWIN1252

             STRING SIZES ARE IN BYTES

             BADFILE ‘mydata.bad’

             NODISCARDFILE

             NOLOGFILE

             SKIP 1

             FIELDS TERMINATED BY ','

             OPTIONALLY ENCLOSED BY '"' AND '"'

             NOTRIM

       )

     LOCATION (UNIX_FLATFILE:'mydata.csv')

  )

REJECT LIMIT UNLIMITED;

External tables can be used to load data to a database, where you have to do some data manipulation. This avoids couple of steps in the conventional method, where you would load the data to a temporary table and then load the destination tables using queries.

Oracle9i's external tables are a complement to the existing SQL*Loader functionality, and are especially useful for environments where the complete external source has to be joined with existing database objects and transformed in a complex manner, or where the external data volume is large and used only once. SQL*Loader, on the other hand, might still be the better choice for loading of data where additional indexing of the staging table is necessary. This is true for operations where the data is used in independent complex transformations or the data is only partially used in further processing.

Dictionary Views that show external table information:

SQL >desc dba_external_tables

 Name                            Null?    Type

 ------------------------------- -------- ------------

 OWNER                           NOT NULL VARCHAR2(30)

 TABLE_NAME                      NOT NULL VARCHAR2(30)

 TYPE_OWNER                               CHAR(3)

 TYPE_NAME                       NOT NULL VARCHAR2(30)

 DEFAULT_DIRECTORY_OWNER                  CHAR(3)

 DEFAULT_DIRECTORY_NAME          NOT NULL VARCHAR2(30)

 REJECT_LIMIT                             VARCHAR2(40)

 ACCESS_TYPE                              VARCHAR2(7)

 ACCESS_PARAMETERS                        VARCHAR2(4000)

 

SQL >

SQL >desc dba_external_locations

 Name                            Null?    Type

 ------------------------------- -------- ----------------

 OWNER                           NOT NULL VARCHAR2(30)

 TABLE_NAME                      NOT NULL VARCHAR2(30)

 LOCATION                                 VARCHAR2(4000)

 DIRECTORY_OWNER                          CHAR(3)

 DIRECTORY_NAME                           VARCHAR2(30)

 

SQL >