External Tables

This is a feature I use a lot…

An external table in Oracle is a table that its structure is defined in the data dictionary, but its content is taken from a standalone file, and is not stored inside the database. This feature is very useful as part of an ETL process, where you need to load the content of some files into the database.

The external file must be located in the file system of the database server machine (or at least in a directory that it can access). This is the major drawback I see in this feature with respect to its predecessor, SQL*Loader (which loads files located at the client machine). But the advantages are significantly superior to the disadvantages.

The definition of the external table describes how to locate, parse and process the file: its name, the directory it resides in, the mapping between the physical fields and the logical columns, the field names and data types, how rows are delimited, how fields are delimited, if it has a header and how many rows it takes, the content’s character set, etc.

Once the table is created, you can simply select from the table as if it was a “real” table – applying functions and expressions, joining it with other tables (external or internal), and so on.

For example, we created a COUNTRIES table, with a name and a 2-character code for each country:

create table countries (
  code varchar2(2)    not null primary key,
  name nvarchar2(100) not null
);

We have a file – countries.txt – containing the following data:

Country Name;Country Code
ISRAEL;IL
UNITED KINGDOM;GB
UNITED STATES;US
AUSTRALIA;AU
CANADA;CA
...

We’d like to fill the table with the file’s content. First, we’ll create two DIRECTORY objects, mapped to directories on the database server: one for reading the countries.txt file from, and the second for log and bad files. Obviously, one directory can be used for both functions, but I prefer having a read-only directory for the actual data and a read-write directory for the log files:

create directory external_files_dir as '/tmp/ext_dir';
create directory external_log_dir as '/tmp/log_dir';
grant read on directory external_files_dir to my_user;
grant read,write on directory external_log_dir to my_user;

Now let’s create an external table:

create table countries_ext (
       name nvarchar2(100),
       alpha2 varchar2(100)
   ) 
   organization external (
     type oracle_loader
     default directory external_files_dir
     access parameters (
       records delimited by newline
       characterset utf8
       skip 1
       logfile external_log_dir:'countries_%p.log'
       badfile external_log_dir:'countries_%p.txt'
       fields terminated by ';' optionally enclosed by '"'
       missing field values are null
       reject rows with all null fields 
     )
     location ('countries.txt')
   )
   reject limit unlimited;

Some notes about this CREATE TABLE statement:

• name and alpha2 are the names we give to the first and second fields in the file rows, respectively
• skip 1 means that the first line of the file will be ignored (because it contains the header, which we don’t need)
• logfile and badfile define files that will be populated every time the external table is queried. logfile contains general information about the reading process of the file, and badfile contains rows from the file that could not be loaded. %p will be replaced with the process ID of the process executing the query.
• records delimited by specifies what is the line terminator
• fields terminated by specifies what is the separator between fields in the same line
• For more options see the documentation

Now we only need to make sure the directories /tmp/ext_dir and /tmp/log_dir exist, to  put the countries.txt file in /tmp/ext_dir, and we can fill our COUNTRIES table with data selected from the countries.txt file via the COUNTRIES_EXT external table:

insert into countries (code,name)
select alpha2,name from countries_ext;