New Oracle9i external tables   Donald K. Burleson

In one exciting nature of Oracle9i is the ability to take data directly from operating system files and make it appear to Oracle as if it were a table inside the database.  Oracle9i has extended its interfaces with the operating system to allow any type of flat file to behave as if it were a relational table, allowing you to write virtually any kind of SQL against a house standard relational table.

In fact, you can even take MS-Excel spreadsheet files (.xls files) and make them appear to be tables inside Oracle9i.

This external table functionality is especially useful for Oracle data warehouses where metadata commonly comes in frequently.  Rather than taking the time to use Oracle SQL*Loader utility to put the data into the database, the Oracle9i DBA can leave the information in the flat file and create an external table upon the flat file.

Using this technique, the information will behave as if it were part of the Oracle database, when in reality on the information is external to Oracle.

Here is a simple example.  Here we start with a comma-delimited flat file.

7369,SMITH,CLERK,7902,17-DEC-80,800,20

7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30

7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30

7566,JONES,MANAGER,7839,02-APR-81,2975,,20

7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30

7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30

7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10

7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20

7839,KING,PRESIDENT,,17-NOV-81,5000,,10

7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30

7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20

Using the following commands, we can define this file as an Oracle table:

Create directory blah as ‘/home4/teach17’

create table external_emp (

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2))

Organization external

(type oracle_loader

default directory BLAH

access parameters (records delimited by newline

fields terminated by ‘,’)

location (‘extemp.txt’))

reject limit 1000;

Now, we can issue any SQL against the table.  However, there are some important limitations to external tables:

• You cannot “select *” from external tables

• You cannot use DML

• Not good for high-volume queries

• Good for metadata tables in warehouses

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm