External Tables

External tables uses the SQL*Loader functionality to access data in the operating system files without ever loading the data into a real oracle table. It is possible to read and write to a external table. Basically the data dictionary holds the table definition but the data remains in the o/s file but you can access the table just like any other table. External table offers some advantages over SQL*Loader

One small note is that you cannot index a external table, so high powered query work is impractical, if you must have indexes then use SQL*Loader and load the data into staging table or a temporary table. External tables are heavy used in data warehousing environments.


create table sales_ext (
 product_id number(6),
 sale_date date,
 store_id number(8),
 quantity_sold number(8)
organization external (
  type oracle_loader                    /* oracle access driver (see below) */
  default directory ext_data_dir        /* oracle directory object */
  access parameters                     /* these are similar to the SQL*Loader parms */
  (records delimited by newline
     badfile log_file_dir:'sales.bad_txt'
     logfile log_file_dir:'sales.log_txt'
     fields terminated by "|" LDRTRIM
     missing field values are null
 location ('sales.data')                /* data file located in the oracle directory */
reject limit unlimited;

Note: there are two types of access driver oracle_loader (load only) or oracle_datapump (load or unload)

Creating (using datapump) create table test (
 product_id number(6),
 warehouse_id number(3),
 quantity_on_hand number (8)
organization external (
 type oracle_datapump
 default directory ext_data_dir
 location ('test.dmp')
Writing (using datapump) create table test
organization external (
 type oracle_datapump
 default directory ext_data_dir
 location ('test.dmp'))
select * from scott.dept;
get SQL*Loader to generate the creation statement

sqlldr userid=vallep control=test.ctl external_table=generate_only log=log.txt

Note: the creation statement will be located on the log file, obviously you have to create the SQL*Loader control file.

See DataPump for more information