External tables in Oracle

External tables can read flat files (that follow some rules) as though they were ordinary (although read-only) Oracle tables. Therefore, it is convinient to use external tables to load flat files into the DB.

The following two examples show how to 'import' a file with external tables.

The first file consists of 4 records whose fields (attributes) are seperated by commas:

1,one,first
2,two,second
3,three,third
4,four,fourth

The second file consists of 4 records whose first field is a fixed length field of 4 characters.

B000Albert
B001Basil
B002Caesar
B003Darius

In order to reference these files, a directory must be created.

create or replace directory ext_dir as '/home/rene/ext_dir';

Of course, /home/rene/ext_dir must point to the directory where the file actually resides.

In order to prevent a ORA-29913, someone must grant read and write on the directory to the user that uses the directory:

grant read, write on directory ext_dir to rene;

It should be self explanatory that you cannot (in most cases) grant this privilege to yourself.

Comma delimited

Now, we're ready to create the table. We assume that the file name is file.csv.

create table ext_table_csv (
  i   Number,
  n   Varchar2(20),
  m   Varchar2(20)
)
organization external (
  type              oracle_loader
  default directory ext_dir
  access parameters (
    records delimited  by newline
    fields  terminated by ','
    missing field values are null
  )
  location ('file.csv')
)
reject limit unlimited;

It's now possible to select from ext_table_csv:

select * from ext_table_csv;

Fixed field length

create table ext_table_fixed (
   field_1 char(4),
   field_2 char(30)
)
organization external (
   type       oracle_loader
   default directory ext_dir
   access parameters (
     records delimited by newline
     fields (
       field_1 position(1: 4) char( 4),
       field_2 position(5:30) char(30)
    )
  )
  location ('file')
)
reject limit unlimited;

It's now possible to select from ext_table_fixed:

select * from ext_table_fixed;

In the directory specified with ext_dir, a log file will as well be written upon selecting from the external table.

Reading the alert log through an external table

Here is a procedure which creates an external table that can be used to read the alert.log.

Links

See also Loading dates with external tables in Oracle that shows how to specify a date format mask.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback