Beiträge getaggt mit external tables
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