The External Table Basics

The External Table basics

An External Table is basically a file that resides on the server side, as a regular flat file or as a data pump formatted file. The External Table is not a table itself; it is an external file with an Oracle format and its physical location.  This feature first appeared back in Oracle 9i Release 1 and it was intended as a way of enhancing the ETL process by reading an external flat file as if it was a regular Oracle table.

On its initial release it was only possible to create read-only External Tables, but, starting with 10g--it is possible to unload data to External Tables too.

In previous 10g Releases there was only the SQL*Loader driver could be used to read the External Table, but from 10g onwards it is now possible to load the table by means of the data pump driver. The kind of driver that will be used to read the External Table is defined at creation time. In the case of ORACLE_LOADER it is the same driver used by SQL*Loader. The flat files are loaded in the same way that a flat file is loaded to the database by means of the SQL*Loader utility, and the creation script can be created based on a SQL*Loader control file. In fact, most of the keywords that are valid for data loading are also valid to read an external flat file table.

The main differences between SQL*Loader and External Tables are:

· When there are several input datafiles SQL*Loader will generate a bad file and a discard file for each datafile.
· The CONTINUEIF and CONCATENATE keywords are not supported by External Tables.
· The GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC are not supported for External Tables.
· LONG, nested tables, VARRAY, REF, primary key REF, and SID are not supported.
· For fields in External Tables the character set, decimal separator, date mask and other locale settings are determined by the database NLS settings.
· The use of the backslash character is allowed for SQL*Loader, but for External Tables this would raise an error. External Tables must use quotation marks instead.

For example:

A second driver is available, the ORACLE_DATAPUMP access driver, which uses the Data Pump technology to read the table and unload data to an External Table. This driver allows the user to perform a logical backup that can later be read back to the database without actually loading the data. The ORACLE_DATAPUMP access driver utilizes a proprietary binary format for the external file, so it is not possible to view it as a flat file.