External Tables

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 >

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.