Pickleball spielen

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