An Introduction to External Tables

Oracle external tables allow you to interrogate operating system files from within a database. Before starting this example, I made a copy of the database's alert log, called it external_file and put it in a directory called /usr/users/oracle/andrew/external_dir. The next step is to create a directory inside the database and link it to the directory in the operating system:

SQL> col external_line format a40
SQL> conn / as sysdba
Connected.
SQL> create or replace directory external_dir
  2  as '/usr/users/oracle/andrew/external_dir'
  3  /

Directory created.

SQL>

Then you need to create the external table, which will look at the copy of the alert log:

SQL> create table external_table
  2  (
  3    external_line varchar2(1000)
  4  )
  5  organization external
  6  (
  7    type oracle_loader
  8    default directory external_dir
  9    access parameters
 10    (
 11      records delimited by newline
 12      badfile 'external_data.bad'
 13      discardfile 'external_data.dis'
 14      logfile 'external_data.log'
 15      fields terminated by "~"
 16      (
 17        external_line char(1000)
 18      )
 19    )
 20    location ('external_file')
 21  )
 22  reject limit unlimited
 23  /

Table created.

SQL>

The alert log contains details of the database's character set so it should be possible to see these by looking in the external table:

SQL> select distinct external_line
  2  from external_table
  3  where external_line like '%CHARACTER SET%'
  4  /

EXTERNAL_LINE
----------------------------------------
CHARACTER SET WE8ISO8859P1

SQL>

Several actions are not permitted on external tables. For example, you cannot create an index. I guess that's because Oracle has no way no way of knowing if the contents of the external table have changed and the index needs updating. If you try any of these actions, you get an ORA-30657:

SQL> create index external_table_index
  2  on external_table (external_line)
  3  /
on external_table (external_line)
  *
ERROR at line 2:
ORA-30657: operation not supported on external
organized table

SQL> insert into external_table
  2  select 'One more line' from dual
  3  /
insert into external_table
            *
ERROR at line 1:
ORA-30657: operation not supported on external
organized table

SQL> delete external_table
  2  /
delete external_table
      *
ERROR at line 1:
ORA-30657: operation not supported on external
organized table

SQL> update external_table
  2  set external_line = 'Updated data'
  3  /
update external_table
      *
ERROR at line 1:
ORA-30657: operation not supported on external
organized table

SQL>