Note that to create a directory, we require the CREATE ANY DIRECTORY system privilege. Directories are system-wide, so if we cannot get the privilege, a DBA can create the directory and grant READ or WRITE to our users as required (READ for external table users and READ/WRITE as appropriate for UTL_FILE users).
We will reference this directory in our external table definition as follows. Some explanation of the new syntax follows the table creation.
SQL> CREATE TABLE emp_xt
2 ( empno NUMBER(4)
3 , ename VARCHAR2(10)
4 , job VARCHAR2(9)
5 , mgr NUMBER(4)
6 , hiredate DATE
7 , sal NUMBER(7,2)
8 , comm NUMBER(7,2)
9 , deptno NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 (
13 TYPE ORACLE_LOADER
14 DEFAULT DIRECTORY xt_dir
15 ACCESS PARAMETERS
16 (
17 RECORDS DELIMITED by NEWLINE
18 BADFILE 'emp_xt.bad'
19 LOGFILE 'emp_xt.log'
20 NODISCARDFILE
21 FIELDS TERMINATED BY ','
22 ( empno
23 , ename
24 , job
25 , mgr
26 , hiredate CHAR(20) DATE_FORMAT DATE MASK "DD/MM/YYYY"
27 , sal
28 , comm
29 , deptno
30 )
31 )
32 LOCATION ('emp.dat')
33 )
34 REJECT LIMIT UNLIMITED;
Table created.
We have now created an external table and we can see a wide range of new and extended syntax. In particular, we can see something that looks similar to (but not quite the same as) a SQL*Loader control file. Some points to note are as follows.
- Line 11: the ORGANIZATION EXTERNAL clause tells Oracle that we are creating an external table;
- Line 13: the ORACLE_LOADER driver is a new type that "powers" external tables;
- Line 14: we can set a DEFAULT DIRECTORY once for the entire table if we wish. This states that, unless we tell Oracle otherwise, all files created or read as part of this table will reside in this named directory. In most cases, we will wish to write log/bad/discardfiles to a logging directory and read our incoming data files from a data directory. For simplicity, we have used a single XT_DIR in our examples for all files;
- Lines 15-31: the ACCESS PARAMETERS clause contains the SQL*Loader-style reference to enable Oracle to parse the flat-file into rows and columns. At this time, external tables do not offer the extensive range of parse options that SQL*Loader provides, yet still cater for most loading requirements. Note that if we have made any syntactical errors in our ACCESS PARAMETERS, we can still create the external table. Access parameters themselves are not parsed until we issue a SELECT against the external table;
- Lines 22-30: in this example, we have listed each of the fields in the file and table. Like SQL*Loader, the ORACLE_LOADER external table driver assumes that all incoming fields are CHAR(255) unless we state otherwise. In the EMP_XT table, we have a DATE column that needs to be converted as on line 26, hence the need to list all fields. Otherwise, we can simply use the default parsing specified in the FIELDS clause, which in our simple example only lists the field delimiter;
- Line 32: the LOCATION clause is where we specify the input file(s). Note that we don't have a file named "emp.dat" at the time of table creation; this is not necessary (we shall discuss this later); and
- Line 34: similar to the ERRORS= clause of SQL*Loader, we can specify a REJECT LIMIT for an external table. This is the number of bad records we will tolerate before the load is failed.
using external tables
Now we have created our EMP_XT external table, we can use it as follows. Remember at this stage we don't have the emp.dat file that the table expects (as per the LOCATION) clause). We can generate a simple csv-file from the existing EMP table as follows (using the oracle-developer.net DATA_DUMP utility).
SQL> BEGIN
2 data_dump( query_in => 'SELECT * FROM emp',
3 file_in => 'emp.dat',
4 directory_in => 'XT_DIR',
5 nls_date_fmt_in => 'DD/MM/YYYY' );
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> host dir d:\oracle\dir\emp.dat
Volume in drive D is USER
Volume Serial Number is 7476-8930
Directory of d:\oracle\dir
04/08/2002 18:57 633 emp.dat
1 File(s) 633 bytes
0 Dir(s) 26,696,744,960 bytes free
Now we are ready to select from our external table.
SQL> SELECT * FROM emp_xt;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/1980 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 1250 500 30
7566 JONES MANAGER 7839 02/04/1981 2975 20
7654 MARTIN SALESMAN 7698 28/09/1981 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 2850 30
7782 CLARK MANAGER 7839 09/06/1981 2450 10
7788 SCOTT ANALYST 7566 09/12/1982 3000 20
7839 KING PRESIDENT 17/11/1981 5000 10
7844 TURNER SALESMAN 7698 08/09/1981 1500 0 30
7876 ADAMS CLERK 7788 12/01/1983 1100 20
7900 JAMES CLERK 7698 03/12/1981 950 30
7902 FORD ANALYST 7566 03/12/1981 3000 20
7934 MILLER CLERK 7782 23/01/1982 1300 40
14 rows selected.
It is clear from the above that if we can query a flat-file via an external table, then we have fundamentally changed the way in which we can load external data into our database! In fact, we may not even need to load the file at all as we can query it directly (why bother to stage data that will be used once and replaced every day?).
External Table metadata
We can see information about our EMP_XT table in several dictionary views as follows (note that XXX is a placeholder for USER, ALL and DBA).
- XXX_TABLES;
- XXX_ALL_TABLES;
- XXX_EXTERNAL_TABLES; and
- XXX_EXTERNAL_LOCATIONS.
The last two are specific to external tables only. For general information on our external table we can query USER_EXTERNAL_TABLES, which is structured as follows.
SQL> desc USER_EXTERNAL_TABLES
Name Null? Type
----------------------------------- -------- -----------------
TABLE_NAME NOT NULL VARCHAR2(30)
TYPE_OWNER CHAR(3)
TYPE_NAME NOT NULL VARCHAR2(30)
DEFAULT_DIRECTORY_OWNER CHAR(3)
DEFAULT_DIRECTORY_NAME NOT NULL VARCHAR2(30)
REJECT_LIMIT VARCHAR2(40)
ACCESS_TYPE VARCHAR2(7)
ACCESS_PARAMETERS VARCHAR2(4000)
As its name suggests, the XXX_EXTERNAL_LOCATIONS views list the file(s) that an external table is currently "pointed to". We can read multiple files via a single external table and these can be in different directories. Currently, our EMP_XT location is as follows.
SQL> SELECT *
2 FROM user_external_locations
3 WHERE table_name = 'EMP_XT';
TABLE_NAME LOCATION DIRECTORY_OWNER DIRECTORY_NAME
------------ ------------ ----------------- ----------------
EMP_XT emp.dat SYS XT_DIR
1 row selected.
Modifying Location
Remember that the emp.dat file did not exist at the time we created the EXP_XT table. If we try to query an external table that has an incorrect location clause, we receive the following error.
SQL> host del d:\oracle\dir\emp.dat
SQL> host dir d:\oracle\dir\emp.dat
Volume in drive D is USER
Volume Serial Number is 7476-8930
Directory of d:\oracle\dir
File Not Found
SQL> SELECT * FROM emp_xt;
SELECT * FROM emp_xt
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp.dat in XT_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
Our EMP_XT external table currently has a single incoming flat-file (emp.dat) and this doesn't exist. In operational systems, we are more likely to receive new files every day and these are often uniquely named to distinguish one day's delivery from another (e.g. with a business date of some format in the file name). The location clause of an external table can be modified to cater for this without invalidating any dependencies (such as views or packages).
In the following example, we will create an emp_20020804.dat file and modify the EMP_XT table to reference this new file. We will complete the example by selecting from it.
SQL> BEGIN
2 data_dump( query_in => 'SELECT * FROM emp',
3 file_in => 'emp_20020804.dat',
4 directory_in => 'XT_DIR',
5 nls_date_fmt_in => 'DD/MM/YYYY' );
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> ALTER TABLE emp_xt LOCATION ('emp_20020804.dat');
Table altered.
SQL> SELECT * FROM emp_xt;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/1980 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 1250 500 30
7566 JONES MANAGER 7839 02/04/1981 2975 20
7654 MARTIN SALESMAN 7698 28/09/1981 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 2850 30
7782 CLARK MANAGER 7839 09/06/1981 2450 10
7788 SCOTT ANALYST 7566 09/12/1982 3000 20
7839 KING PRESIDENT 17/11/1981 5000 10
7844 TURNER SALESMAN 7698 08/09/1981 1500 0 30
7876 ADAMS CLERK 7788 12/01/1983 1100 20
7900 JAMES CLERK 7698 03/12/1981 950 30
7902 FORD ANALYST 7566 03/12/1981 3000 20
7934 MILLER CLERK 7782 23/01/1982 1300 40
14 rows selected.
Investigating Errors
External tables can optionally generate various log files in the same manner as SQL*Loader. In our EMP_XT table, remember that we opted to create a logfile and badfile but didn't need a discardfile (as we do not have a LOAD WHEN clause). For developers who are unfamiliar with these three SQL*Loader-style files, their short descriptions are:
- logfile: contains information on how the input files were parsed, the positions and error messages for any rejected records and some other general information on the load such as the number of row successfully read;
- badfile: contains the rejected records (i.e. the records that couldn't be loaded for the reasons given in the logfile); and
- discardfile: contains the records that failed the LOAD WHEN clause (this clause is a simple filter to prevent records with certain data characteristics from being loaded).
To investigate errors with an external table read or load, therefore, we have the same information available to us as we did with SQL*Loader. It is likely that, when putting together access parameters for the first few times, we will make mistakes. Oracle will not parse the parameters when we create the table; rather they will be invoked when we try to read from the external table (i.e. SELECT from it). Any syntactical errors will show up as a KUP-% message and will need to be investigated in line with the online documentation (link provided at the end of this article). Errors with the data, however, can be investigated by reference to the output files.
Note that logfiles are appended on each select from an external table so in a regular batch system we might wish to uniquely name each output file for support and diagnostic purposes. Oracle provides two switches to append to the file names in the LOGFILE, BADFILE and DISCARDFILE clauses. These are %p (process ID) and %a (agent number for parallel query) and are specified in the access parameters (e.g. LOGFILE 'emp_xt_%p.log'). Assuming each select from the external table is performed in a new session, each output file is generated with a new process ID.
It is unfortunate that we cannot use ALTER TABLE directly to modify the output file names as we are more likely to want to append a date stamp to them (we can't do this because the file names are part of the ACCESS PARAMETERS clause). We can, however, take advantage of the fact that we can alter the access parameters to build a utility based somewhat loosely on the following example. This is a very rough demonstration of the principles we can adopt to make a log/bad/discardfile naming utility (obviously a "real" utility would be far more flexible and parameterised). The following anonymous block finds the existing logfile name and replaces it with "today's" logfile before using the external table.
SQL> DECLARE
2
3 v_access VARCHAR2(32767);
4 v_replace VARCHAR2(128);
5 v_oldfile VARCHAR2(128);
6 v_newfile VARCHAR2(128) := 'emp_20020804.log';
7
8 BEGIN
9
10 SELECT access_parameters INTO v_access
11 FROM user_external_tables
12 WHERE table_name = 'EMP_XT';
13
14 IF INSTR(v_access,'NOLOGFILE') > 0 THEN
15 v_access := REPLACE(
16 v_access, 'NOLOGFILE',
17 'LOGFILE ''' || v_newfile || ''''
18 );
19 ELSE
20 v_oldfile := SUBSTR(
21 v_access,
22 INSTR(v_access,'LOGFILE')+8,
23 (INSTR(v_access,'.log')+4)-(INSTR(v_access,'LOGFILE')+7)
24 );
25 v_replace := REPLACE(REPLACE(v_oldfile, '"', ''''), '''');
26 v_access := REPLACE(v_access, v_replace, v_newfile);
27 END IF;
28
29 EXECUTE IMMEDIATE
30 'ALTER TABLE emp_xt ACCESS PARAMETERS (' || v_access || ')';
31
32 END;
33 /
PL/SQL procedure successfully completed.
SQL> SELECT access_parameters
2 FROM user_external_tables
3 WHERE table_name = 'EMP_XT';
ACCESS_PARAMETERS
-----------------------------------------------------------------------
RECORDS DELIMITED by NEWLINE
BADFILE 'emp_xt.bad'
LOGFILE 'emp_20020804.log'
NODISCARDFILE
FIELDS TERMINATED BY ','
( empno
, ename
, job
, mgr
, hiredate CHAR(20) DATE_FORMAT DATE MASK "DD/MM/YYYY"
, sal
, comm
, deptno
)
1 row selected.
SQL> SELECT COUNT(*) FROM emp_xt;
COUNT(*)
----------
14
1 row selected.
SQL> host dir d:\oracle\dir\emp*
Volume in drive D is USER
Volume Serial Number is 7476-8930
Directory of d:\oracle\dir
04/08/2002 19:09 633 emp_20020804.dat
04/08/2002 21:54 2,558 emp_20020804.log
04/08/2002 19:04 11,850 emp_xt.log
3 File(s) 15,041 bytes
0 Dir(s) 26,696,704,000 bytes free
Advantages Over SQL*Loader
We have seen some examples of external table syntax but have not yet explored why we might use them over SQL*Loader. It is the case that SQL*Loader can parse and load almost any flat-file we wish to throw at it. External tables, on the other hand, cater for the more common processing requirements. Despite this, their advantages over SQL*Loader are numerous, some of which are as follows:
- Ease of use: External tables can be selected, sorted, filtered, joined, intersected, minused, unioned and so on using SQL, the language most familiar database to Oracle developers. Anything we can do in a SELECT statement with a "normal" table can be done with an external table. This makes working with external flat-files very simple;
- Performance (1): reads/loads involving external tables can be paralleled When combined with direct path (in the case of INSERTs), this dramatically outperforms SQL*Loader which has to load in serial mode. Parallelism can be set at the external table level or more selectively in hints;
- Performance (2): as seen above, loading a table from an external table is faster than SQL*Loader due to parallel query and DML. In addition to this, ETL processes that read directly from external tables rather than pre-loaded staging tables are faster because they do not incur the SQL*Loader step (i.e. data is only read once). For example, if an ETL process takes 10 minutes in SQL*Loader and 10 minutes in SQL or PL/SQL loading, using external tables directly in the latter process can eradicate up to 50% of the ETL time;
- Disk Space: External tables do not require any database space; only the space consumed by the flat-files on the filesystem. SQL*Loader requires two copies of each file (one inside the database in a staging table), so external tables are "cheaper" on disk;
- Error-trapping: SQL*Loader returns different codes based on its outcome. For batch systems, this can be tricky because the error code is sometimes ambiguous. For example, exit code 2 means "a bad file has been created". Of course, this may or may not be a cause for concern. If we allow 50 bad records (errors=50), then the fact that a bad file contains 1-49 bad records should not signal a batch failure. We therefore need to write some clever code to interrogate the badfile or logfile to determine whether this is a "bad code 2" or "acceptable code 2". With external tables, it is much more simple. Until we reach the REJECT LIMIT, the SQL statement continues or completes successfully. If we reach this limit, the statement fails;
- Debugging and support: External tables are equally useful as debugging and support aids. For example, we can create further external tables over logfiles and badfiles to investigate errors easily with SQL. DBAs can also create external tables over critical files such as the alert log.
There are, however, a few issues to be aware of that might limit the scope of how we use these tables. For example, external tables can only have one location set at a time (i.e. different sessions cannot share a table but set different locations). This means that their use for loading is serialised for the length of time a specific location is required and in use (and probably should be protected as such). If multiple sessions need to share the same table but load different files at the same time, then either multiple tables must be created or some form of locking will be required. For the latter scenario, the length of time a table is locked should be reduced to a minimum to maximise concurrency. For this reason, multi-user external tables will probably not figure in long-running batch processes.
In addition to this, external tables cannot be indexed. For most staging tables, this won't be an issue as we would prefer hash joins for ETL batch queries, but in some cases indexes might be required. For these, it would probably be sensible to use external tables to load the physical staging tables rather than use them directly in the ETL queries.
More on Performance
All external table reads are direct path and with direct path inserts and parallel DML, external tables will usually be quicker than SQL*Loader (which is serial). We've also seen that ETL processes involving external tables can be faster because they do not require the "lead-in" time of loading a staging table first. However, we have not yet looked at external tables compared with "internal" tables (note that this how Oracle described such tables in the SQL*Loader logfile above). In the following simple example, we will generate 1 million records in a heap table and a flat-file and compare the time taken to scan this data.
First we will create a large table based on 1 million EMP records as follows.
SQL> CREATE TABLE million_emps
2 NOLOGGING
3 AS
4 SELECT e1.*
5 FROM emp e1
6 , emp, emp, emp, emp, emp
7 WHERE ROWNUM <= 1000000;
Table created.
We will write these records to a flat-file for our existing EMP_XT table to use.
SQL> BEGIN
2 data_dump( query_in => 'SELECT * FROM million_emps',
3 file_in => 'million_emps.dat',
4 directory_in => 'XT_DIR',
5 nls_date_fmt_in => 'DD/MM/YYYY' );
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> ALTER TABLE emp_xt LOCATION ('million_emps.dat');
Table altered.
Using the wall-clock and autotrace, we will compare a simple fetch of the data from both the MILLION_EMPS and the EMP_XT tables, starting with the "internal" table.
SQL> set timing on
SQL> set autotrace traceonly statistics
SQL> SELECT * FROM million_emps;
1000000 rows selected.
Elapsed: 00:00:18.05
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
72337 consistent gets
5675 physical reads
0 redo size
13067451 bytes sent via SQL*Net to client
733825 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
Next we can test the EMP_XT external table.
SQL> SELECT * FROM emp_xt;
1000000 rows selected.
Elapsed: 00:00:22.08
Statistics
----------------------------------------------------------
192 recursive calls
0 db block gets
6282 consistent gets
0 physical reads
0 redo size
13067451 bytes sent via SQL*Net to client
733825 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1000000 rows processed
We can see that the external table is approximately 22% slower than the internal table on a single read. If we re-run the fetches several times, similar timings are recorded. Note that autotrace does not show any physical reads in its statistics for the external table (this is possibly a bug).
Error Troubleshooting:
