One of the most exciting advances in Oracle9i is the ability to access non-Oracle files with Oracle SQL. This new functionality, called external tables, has important ramifications for systems where external files need to be available for non-database applications and appear to be a table within Oracle.

External tables allow you to define the structure of almost any flat file on your server and have it appear to Oracle as if it were a real table, as we’ve diagrammed in Figure A.

Figure A
Oracle read and write interfaces to OS files

As you can see, Oracle lets a database program write to flat files using the utl_file utility. Combined with external table’s read ability, this new topology removes the requirement that all Oracle data reside inside Oracle tables, opening new applications for Oracle. Let’s take a closer look at how this feature works.

Defining an external table
Let’s say you want Oracle to refer to this 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

The file contains the following employee information:

  • ·        Employee ID
  • ·        Last name
  • ·        Job description
  • ·        Manager’s employee ID
  • ·        Hire date
  • ·        Salary
  • ·        Commission
  • ·        Department

So, how do we define this file to Oracle? First, we must create an Oracle directory entry in the data dictionary that points to the Windows directory where the flat file resides. In this example, we’ll name the directory testdir and point it to c:\docs\pubsdb\queries:
 
SQL> create directory testdir as ‘c:\docs\pubsdb\queries’;
 
Directory Created.
 

Now that we have the directory, we can define the structure of the external file to Oracle. You’ll see this code in Listing A.

In this syntax, we define the column of the external table in much the same way as you would an internal Oracle table. The external definitions occur in the organization external clause, as shown in Table A.
Table A


default directory testdir


The directory where the file resides


records delimited by newline


The new line character


fields terminated by ‘,’


The column termination character


location (‘emp_ext.csv’)


The name of the external file

External definitions for the comma-delimited file

Now that we’ve defined the external table, we can run reports against the external table using SQL, just as if the table resided inside the database. In the query shown in Listing B, note the use of the sophisticated ROLLUP parameter to summarize salaries by both department and job title. The results are available in Listing C.

Limitations on external tables
Because external tables are new, Oracle has not yet perfected their use. In Oracle9i the feature has several limitations, including:

  • ·        No support for DML. External tables are read-only, but the base data can be edited in any text editor.
  • ·        Poor response for high-volume queries. External tables have a processing overhead and are not suitable for large tables.

Looking ahead
Accessing flat files via Oracle has a number of uses. In our next installment, we’ll see how you can define spreadsheets to Oracle. This technique has important ramifications for shops where users can control systemwide parameters inside desktop spreadsheets and Oracle knows immediately about changes.