Brief introduction into External Tables

As an Oracle DBA, you will sooner or later encounter the task to deal with interface files (Flat Files) from some other application that you need to process in your Oracle Database. External Tables, introduced in 9i already, are usually the tool of choice for that and can be considered as the successor of the ancient SQL*Loader for that purpose.

An External Table is Metainformation that is stored in the Data Dictionary together with a pointer on the concrete Flat File, placed on the Database Server. We can access this External Table with the same SELECT statements as we would use against ordinary Tables. I have prepared a demonstration with the Flat File ulcase2.dat in the directory /home/oracle/interface on my server:

-bash-3.00$ pwd
/home/oracle/interface
-bash-3.00$ ls -al
total 16
drwxr-xr-x   2 oracle oinstall 4096 Oct 28 14:34 .
drwxrwxrwx  30 oracle oinstall 4096 Oct 28 14:11 ..
-rw-r--r--   1 oracle oinstall  372 Oct 28 14:11 ulcase2.dat
-bash-3.00$ cat ulcase2.dat
7782 CLARK      MANAGER   7839  2572.50          10
7839 KING       PRESIDENT       5500.00          10
7934 MILLER     CLERK     7782   920.00          10
7566 JONES      MANAGER   7839  3123.75          20
7499 ALLEN      SALESMAN  7698  1600.00   300.00 30
7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30
7658 CHAN       ANALYST   7566  3450.00          20

This is just an ordinary ASCII file that we want to access from our Oracle Database:

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.

That’s my demo user. Granting the role dba makes the demo easier – no need to grant create/read/write directory privileges especially. Next step is to create a directory as a Database Object, pointing to the OS directory:

SQL> create directory interface_dir as '/home/oracle/interface';

Directory created.

The same directories are needed when we use Data Pump to put Database Objects into binary files. Now here comes the External Table:

SQL> create table interface_table
(
 empno number(4),
 ename varchar2(10),
 job varchar2(9),
 mgr number(4),
 sal number(7,2),
 comm number(7,2),
 deptno number(2)
)
organization external
(
 type oracle_loader
 default directory interface_dir
 access parameters
 (
 records delimited by newline characterset we8iso8859p1
 badfile 'INTERFACE_DIR':'interface.bad'
 logfile 'interface.log_xt'
 fields ldrtrim
 (
 empno (1:4) integer external(4),
 ename (6:15) char(10),
 job (17:25) char(9),
 mgr (27:30) integer external(4),
 sal (32:39) integer external(8),
 comm (41:48) integer external(8),
 deptno (50:51) integer external(2)
 )
 )
 location
 (
 'ulcase2.dat'
 )
)
reject limit unlimited;
Table created.

This is no ordinary table made up by extents and blocks but merely a description of the Flat File:

SQL> select * from user_segments;

no rows selected

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
INTERFACE_TABLE

Apart from that, we can access it with SELECT like an ordinary table:

SQL> select * from interface_table;

 EMPNO ENAME      JOB              MGR        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ----------
 7782 CLARK      MANAGER         7839     2572.5                    10
 7839 KING       PRESIDENT                  5500                    10
 7934 MILLER     CLERK           7782        920                    10
 7566 JONES      MANAGER         7839    3123.75                    20
 7499 ALLEN      SALESMAN        7698       1600        300         30
 7654 MARTIN     SALESMAN        7698     1312.5       1400         30
 7658 CHAN       ANALYST         7566       3450                    20

8 rows selected.

We can put functions into the query, can add where condition to filter the result set, can choose only some columns etc. That is why External Tables are much more flexible to use as the old SQL*Loader was.

But never the less, SQL*Loader is still useful today to teach us how to create External Tables :-) If you look into the Online Documentation of the 9i version, you can still find the SQL*Loader case studies there – that vanished in 10g and above. You see the most common cases of interface files there together with a SQL*Loader controlfile to load them.

If you call the SQL*Loader then with the parameter external_table=generate_only, it gives you the create statement for an External Table – a thing that is not so easy to derive from the External Table Online Documenation, unfortunately.

Another nice usage of External Tables is, that we can access the Alert Log file with them from inside the Database without having to logon to the Database Server:

SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
background_dump_dest                 string      /u01/app/oracle/admin/orcl/bdump

SQL> create or replace directory bdump as '/u01/app/oracle/admin/orcl/bdump';

Directory created.

SQL> create table alert_log
 (line1 varchar2(4000))
 organization external
 (type oracle_loader
 default directory bdump
 access parameters
 (records delimited by newline fields missing field values are null)
 location ('alert_orcl.log')
 )
/  

Table created.

Conlusion: If you need to process Interface Files in your Oracle Database, take a closer look at External Tables for that purpose – the SQL*Loader is a little old fashioned compared to them

About these ads

,

  1. #1 by Kamran Agayev on November 22, 2010 - 11:44

    Nice post Uwe. Just want to add that we can get information about external tables using DBA_EXTERNAL_TABLES view and use DBA_EXTERNAL_LOCATIONS view we can get the location of flat files

  2. #2 by laurentschneider on November 22, 2010 - 18:13

    sure sqlldr is old fashion, but it works thru sqlnet.

    Oracle Enterprise Manager recommends to Ensure that no unintended ports are left open, so how will you deliver your files to your database server then if there is no ftp / scp possible to your database server?

  3. #3 by Uwe Hesse on November 26, 2010 - 13:15

    Kamran, thanks for your contribution! I always enjoy your answers on the OTN forum, BTW, although recently I can hardly find the time to go there.

  4. #4 by Uwe Hesse on November 26, 2010 - 13:18

    Laurent, yes that is a valid point for still using sqlldr: You need to have the flat file on the Database Server in order to use external tables. Thank you for pointing that out!

  5. #5 by Rich Berry on September 21, 2011 - 00:04

    Mr Hesee,

    Trying to use external tables in a 11g RAC enviroment using ASM for the db file storage. I cannot figure out how to create a directory on a shared location between the two instances. Cannot find much on the subject in my searching.

  6. #6 by Uwe Hesse on September 21, 2011 - 11:40

    You can do that with ACFS (ASM based Cluster File System). That is a typical usage of it, to put the flat files for External Tables on ACFS so that all RAC nodes can access them, I mean.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,889 other followers

%d bloggers like this: