listing files with the external table preprocessor in 11g

The external table preprocessor was introduced in 11g Release 1 (patchset 11.1.0.7) and formally documented in 11g Release 2. Briefly, the preprocessor enables us to define an executable or shell/batch script that can be used to generate a rowsource for the external table as it is queried. The most obvious use for such a feature is to enable compressed files to be queried directly from external tables and this is described in another oracle-developer.net article (see references below).

Another obvious use for the preprocessor is to solve the common problem of how to list files in directories from within Oracle (i.e. using SQL or PL/SQL). There is no documented built-in method for doing this in Oracle and techniques described on the web include Java stored procedures or an undocumented DBMS_BACKUP_RESTORE procedure. With the introduction of the preprocessor, however, we will demonstrate how directory listings can be achieved with an external table and a preprocessor batch/shell script.

background reading

Developers who are not familiar with the new preprocessor can read this oracle-developer.net article on new features for external tables in 11g, which describes the feature in some detail and how it can be used to query compressed files.

a simple file listing example

To begin, we will create a simple example of a preprocessor directory listing and for this we require the following components:

As a SYSDBA, we will create the Oracle directories and grant the relevant privileges to SCOTT, as follows.

SQL> conn / as sysdba
Connected.

SQL> CREATE DIRECTORY xt_dir AS 'd:\oracle\dir\xt_dir';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY xt_dir TO scott;

Grant succeeded.

SQL> CREATE DIRECTORY bin_dir AS 'd:\oracle\dir\bin_dir';

Directory created.

SQL> GRANT EXECUTE ON DIRECTORY bin_dir TO scott;

Grant succeeded.

We will now create the external table to read a standard DOS directory listing, as follows.

SQL> conn scott/tiger
Connected.

SQL> CREATE TABLE files_xt
  2  ( file_date VARCHAR2(50)
  3  , file_time VARCHAR2(50)
  4  , file_size VARCHAR2(50)
  5  , file_name VARCHAR2(255)
  6  )
  7  ORGANIZATION EXTERNAL
  8  (
  9    TYPE ORACLE_LOADER
 10    DEFAULT DIRECTORY xt_dir
 11    ACCESS PARAMETERS
 12    (
 13       RECORDS DELIMITED BY NEWLINE
 14       LOAD WHEN file_size != '<DIR>'
 15       PREPROCESSOR bin_dir: 'list_files.bat'
 16       FIELDS TERMINATED BY WHITESPACE
 17    )
 18    LOCATION ('sticky.txt')
 19  )
 20  REJECT LIMIT UNLIMITED;

Table created.

Note the following points about this external table:

We now need to create two files; namely the preprocessor script (i.e. list_files.bat) and the location file (i.e. sticky.txt). As this is a Windows system, the preprocessor script needs to be a batch script. In our case, list_files.bat contains the following simple code:


@echo off
dir d:\oracle\diag\rdbms\ora11\ora11\trace

The first line is mandatory for batch scripts and the script itself must be located in the executable Oracle directory (in our case BIN_DIR). Note that for now we have hard-coded the directory we wish to query (i.e. the user dump destination for this instance).

Moving on, we can now create our sticky.txt location file in our Oracle read-write directory (XT_DIR), as follows.

SQL> host echo > d:\oracle\dir\xt_dir\sticky.txt

For this example, the file doesn't need to contain anything. It simply has to exist. We will now query our preprocessor external table to get a file listing for the 11g trace directory that we coded in the list_files.bat script, as follows.

SQL> SELECT * FROM files_xt WHERE ROWNUM <= 5;

FILE_DATE    FILE_TIME    FILE_SIZE    FILE_NAME
------------ ------------ ------------ ----------------------
Volume       in           drive        D
Volume       Serial       Number       is
14/10/2009   22:39        618,508      alert_ora11.log
14/10/2009   22:38        1,474        ora11_cjq0_5148.trc
14/10/2009   22:38        175          ora11_cjq0_5148.trm

5 rows selected.

We can see our directory listing at work, but the data requires some cleanup. The easiest way to do this is with a view, to filter and format the data we require in a consistent manner, as follows.

SQL> CREATE VIEW files_vxt
  2  AS
  3     SELECT file_name
  4     ,      TO_DATE(
  5               file_date||','||file_time,
  6               'DD/MM/YYYY HH24:MI') AS file_time
  7     ,      TO_NUMBER(
  8               file_size,
  9               'fm999,999,999,999') AS file_size
 10     FROM   files_xt
 11     WHERE  REGEXP_LIKE(
 12               file_date,
 13               '[0-9]{2}/[0-9]{2}/[0-9]{4}');

View created.

We can now query our file listing more cleanly, as follows.

SQL> col file_time format a20
SQL> col file_name format a30
SQL> col file_size format 999,999,999

SQL> SELECT * FROM files_vxt WHERE ROWNUM <= 5;

FILE_NAME                      FILE_TIME               FILE_SIZE
------------------------------ -------------------- ------------
alert_ora11.log                14/10/2009 22:39:00       618,508
ora11_cjq0_5148.trc            14/10/2009 22:38:00         1,474
ora11_cjq0_5148.trm            14/10/2009 22:38:00           175
ora11_dbrm_4016.trc            14/10/2009 23:20:00        20,184
ora11_dbrm_4016.trm            14/10/2009 23:20:00           991

5 rows selected.

Of course, because we are using SQL to access our file listing, we can use wildcard searches, date ordering and so on, as the following example demonstrates.

SQL> SELECT *
  2  FROM   files_vxt
  3  WHERE  file_name LIKE '%.trc'
  4  ORDER  BY
  5         file_time DESC;

FILE_NAME                      FILE_TIME               FILE_SIZE
------------------------------ -------------------- ------------
ora11_dbrm_4016.trc            14/10/2009 22:51:00        18,385
ora11_cjq0_5148.trc            14/10/2009 22:38:00         1,474
ora11_lgwr_2444.trc            14/10/2009 22:37:00         1,312
ora11_smon_2040.trc            14/10/2009 22:37:00         1,133
ora11_mmnl_3760.trc            14/10/2009 22:37:00         1,478
ora11_reco_5792.trc            14/10/2009 22:37:00         1,222
ora11_fbda_3052.trc            13/10/2009 17:41:00         1,167
ora11_w000_4796.trc            13/10/2009 06:52:00           905

8 rows selected.

dynamic file listing with preprocessor scripts

The small batch script example above uses a pre-defined directory listing (i.e. it only lists the 11g trace directory files). One of the features of the batch or shell script that we use is that it can read the path and name of the file used in the external table location clause. This means that it is possible to make the directory listing more dynamic by putting the paths to the directory or directories we wish to list inside the external table location file.

To demonstrate this, we will re-create our FILES_XT external table, using a new batch script and location file, as follows.

SQL> DROP TABLE files_xt;

Table dropped.

SQL> CREATE TABLE files_xt
  2  ( file_date VARCHAR2(50)
  3  , file_time VARCHAR2(50)
  4  , file_size VARCHAR2(50)
  5  , file_name VARCHAR2(255)
  6  )
  7  ORGANIZATION EXTERNAL
  8  (
  9    TYPE ORACLE_LOADER
 10    DEFAULT DIRECTORY xt_dir
 11    ACCESS PARAMETERS
 12    (
 13       RECORDS DELIMITED by NEWLINE
 14       LOAD WHEN file_size != '<DIR>'
 15       PREPROCESSOR bin_dir: 'dynamic_list_files.bat'
 16       FIELDS TERMINATED BY WHITESPACE
 17    )
 18    LOCATION ('directories.txt')
 19  )
 20  REJECT LIMIT UNLIMITED;

Table created.

This time we have a batch script in our BIN_DIR named dynamic_list_files.bat and a location file called directories.txt. The batch file contents are quite simple, as follows.


@echo off
for /F %%X in (%1) do dir %%X

This small script reads the external table location file (%1) and each line in the file (/F) is assigned to a variable (%%X) and used as the target for a directory listing (dir %%X). Each line in the location file therefore needs to be a valid directory path. With this method, there is no hard-coding of paths required in the preprocessor script and we can change the contents of the location file to list different directories as and when required.

To test this, we will add the 11g trace file path to the directories.txt file and run a query over our external table, as follows.

SQL> host echo d:\oracle\diag\rdbms\ora11\ora11\trace > d:\oracle\dir\xt_dir\directories.txt

SQL> SELECT *
  2  FROM   files_vxt
  3  WHERE  file_name LIKE '%.trc'
  4  ORDER  BY
  5         file_time DESC;

FILE_NAME                      FILE_TIME               FILE_SIZE
------------------------------ -------------------- ------------
ora11_dbrm_4016.trc            14/10/2009 22:59:00        18,899
ora11_cjq0_5148.trc            14/10/2009 22:38:00         1,474
ora11_lgwr_2444.trc            14/10/2009 22:37:00         1,312
ora11_smon_2040.trc            14/10/2009 22:37:00         1,133
ora11_mmnl_3760.trc            14/10/2009 22:37:00         1,478
ora11_reco_5792.trc            14/10/2009 22:37:00         1,222
ora11_fbda_3052.trc            13/10/2009 17:41:00         1,167
ora11_w000_4796.trc            13/10/2009 06:52:00           905

8 rows selected.

As described above, the batch script is able to read inside the directories.txt file and pass the contents (i.e. a path) to the dir command. If we wish, we can list multiple paths in the location file. In the following example, we will list the files in two different user dump destinations. First we will add the paths to the location file, as follows.

SQL> host echo d:\oracle\admin\ora92\udump > d:\oracle\dir\xt_dir\directories.txt

SQL> host echo d:\oracle\admin\ora102\udump >> d:\oracle\dir\xt_dir\directories.txt

We are now able to list a sample of 9i and 10g trace files from the same external table, below.

SQL> SELECT *
  2  FROM   files_vxt
  3  WHERE  file_name LIKE '%.trc'
  4  AND    ROWNUM <= 10;

FILE_NAME                      FILE_TIME               FILE_SIZE
------------------------------ -------------------- ------------
ora92_ora_3164.trc             29/04/2009 18:32:00   448,045,831
ora92_ora_3340_bv.trc          19/10/2007 18:14:00         9,802
ora92_ora_4364_sctx.trc        19/10/2007 18:23:00        10,536
ora92_ora_4532_lit.trc         19/10/2007 18:14:00        10,799
ora92_ora_4708_bvcs.trc        19/10/2007 18:28:00        10,513
ora92_ora_5156.trc             28/04/2009 18:00:00         1,784
ora102_ora_1344_x2.trc         18/10/2007 19:14:00        36,239
ora102_ora_1544.trc            17/03/2009 23:04:00           934
ora102_ora_1896.trc            13/03/2009 13:19:00           608
ora102_ora_2272.trc            17/11/2008 07:47:00           591

10 rows selected.

We can clearly see trace files from the two different paths we included in our directories.txt location file.

a more secure dynamic method

Being able to dynamically inject any path into the directories.txt location file has security implications. As an alternative, readers might like to consider the following, more secure, method. Rather than a single "injectable" location file for the FILES_XT external table, we can setup individual location files that each contain one hard-coded directory path. These files can be placed in a readonly directory and whenever a directory needs to be listed, the external table's location can be changed to the corresponding location file.

To demonstrate this, we will begin by creating a readonly Oracle directory as follows.

SQL> conn / as sysdba
Connected.

SQL> CREATE DIRECTORY readonly_dir AS 'd:\oracle\dir\readonly_dir';

Directory created.

SQL> GRANT READ ON DIRECTORY readonly_dir TO scott;

Grant succeeded.

We will now add three location files to the READONLY_DIR directory, one for each of the user dump destinations used in the previous examples. Each file contains the path to the directory indicated by the filename.

SQL> host echo d:\oracle\admin\ora92\udump > d:\oracle\dir\readonly_dir\ora92_udump.txt

SQL> host echo d:\oracle\admin\ora102\udump > d:\oracle\dir\readonly_dir\ora102_udump.txt

SQL> host echo d:\oracle\diag\rdbms\ora11\ora11\trace > d:\oracle\dir\readonly_dir\ora11_udump.txt

We will now modify our FILES_XT table to set the default directory to the READONLY_DIR, as follows.

SQL> conn scott/tiger
Connected.

SQL> ALTER TABLE files_xt DEFAULT DIRECTORY readonly_dir;

Table altered.

We are now ready to use the more secure file listing method. We will begin by listing the files in the ORA92 user dump destination, as follows.

SQL> ALTER TABLE files_xt LOCATION ('ora92_udump.txt');

Table altered.

SQL> SELECT * FROM files_vxt WHERE ROWNUM <= 5;

FILE_NAME                      FILE_TIME               FILE_SIZE
------------------------------ -------------------- ------------
ora92_ora_3164.trc             29/04/2009 18:32:00   448,045,831
ora92_ora_3340_bv.trc          19/10/2007 18:14:00         9,802
ora92_ora_4364_sctx.trc        19/10/2007 18:23:00        10,536
ora92_ora_4532_lit.trc         19/10/2007 18:14:00        10,799
ora92_ora_4708_bvcs.trc        19/10/2007 18:28:00        10,513

5 rows selected.

To query the ORA102 and ORA11 destinations, we simply change the location of the external table to the corresponding (and more importantly, controlled) location files, as follows.

SQL> ALTER TABLE files_xt LOCATION ('ora102_udump.txt');

Table altered.

SQL> SELECT * FROM files_vxt WHERE ROWNUM <= 5;

FILE_NAME                      FILE_TIME               FILE_SIZE
------------------------------ -------------------- ------------
ora102_ora_1344_x2.trc         18/10/2007 19:14:00        36,239
ora102_ora_1544.trc            17/03/2009 23:04:00           934
ora102_ora_1896.trc            13/03/2009 13:19:00           608
ora102_ora_2272.trc            17/11/2008 07:47:00           591
ora102_ora_2564.trc            20/08/2008 07:39:00           591

5 rows selected.

SQL> ALTER TABLE files_xt LOCATION ('ora11_udump.txt');

Table altered.

SQL> SELECT * FROM files_vxt WHERE ROWNUM <= 5;

FILE_NAME                      FILE_TIME               FILE_SIZE
------------------------------ -------------------- ------------
alert_ora11.log                14/10/2009 22:39:00       618,508
ora11_cjq0_5148.trc            14/10/2009 22:38:00         1,474
ora11_cjq0_5148.trm            14/10/2009 22:38:00           175
ora11_dbrm_4016.trc            14/10/2009 23:25:00        20,441
ora11_dbrm_4016.trm            14/10/2009 23:25:00         1,003

5 rows selected.

This method is equally as flexible from an application's perspective, but it gives the DBA more control over which directories are available for listing and who can use the utility. Be aware, however, that if the application has access to any read-write directories, it is still possible for the external table's default directory to be altered and the less secure method used instead. If this is the case, it might be more secure to create a dedicated external table for each directory that needs to be listed and ensure that the application is unable to alter the external tables' directories or locations.

using a pipelined function for dynamic file listing

Finally, we will take the dynamic file listing to a logical conclusion and create a pipelined function to perform the actions in the previous example. For this, we will need to create some types to describe our pipelined function's output data, as follows.

SQL> CREATE TYPE file_listing_ot AS OBJECT
  2  ( file_path VARCHAR(1000)
  3  , file_name VARCHAR2(100)
  4  , file_time DATE
  5  , file_size NUMBER
  6  );
  7  /

Type created.

SQL> CREATE TYPE file_listing_ntt AS TABLE OF file_listing_ot;
  2  /

Type created.

As we can see, the function will return the basic file listing information, together with the path of the directory being listed. We will now create a simple pipelined function to list the files, as follows.

SQL> CREATE FUNCTION list_files(
  2                  p_directory_file IN VARCHAR2
  3                  ) RETURN file_listing_ntt
  4                    AUTHID CURRENT_USER
  5                    PIPELINED IS
  6
  7     v_path VARCHAR2(1000);
  8
  9     ----------------------------------------------------------
 10     PROCEDURE get_path( p_file IN  VARCHAR2,
 11                         p_path OUT VARCHAR2 ) IS
 12        v_file UTL_FILE.FILE_TYPE;
 13     BEGIN
 14        v_file := UTL_FILE.FOPEN('READONLY_DIR', p_file, 'r');
 15        UTL_FILE.GET_LINE(v_file, p_path);
 16        UTL_FILE.FCLOSE(v_file);
 17     END get_path;
 18
 19     ----------------------------------------------------------
 20     PROCEDURE set_location( p_file IN VARCHAR2 ) IS
 21        PRAGMA AUTONOMOUS_TRANSACTION;
 22     BEGIN
 23        EXECUTE IMMEDIATE
 24           'ALTER TABLE files_xt LOCATION (''' || p_file || ''')';
 25     END set_location;
 26
 27  BEGIN
 28
 29     /* Read the path from the directory file... */
 30     get_path( p_directory_file, v_path );
 31
 32     /* Prepare the external table... */
 33     set_location( p_directory_file );
 34
 35     /* Read the file listing... */
 36     FOR r_files IN (SELECT * FROM files_vxt) LOOP
 37        PIPE ROW ( file_listing_ot( v_path,
 38                                    r_files.file_name,
 39                                    r_files.file_time,
 40                                    r_files.file_size ));
 41     END LOOP;
 42     RETURN;
 43  END list_files;
 44  /

Function created.

This function wraps the previous interactive example and performs three simple tasks:

We will now query the pipelined function to get a file listing of the user dump destination for the ORA92 instance on this system.

SQL> SELECT *
  2  FROM   TABLE(list_files('ora92_udump.txt'))
  3  ORDER  BY
  4         file_size DESC;

FILE_PATH                      FILE_NAME                 FILE_TIME               FILE_SIZE
------------------------------ ------------------------- -------------------- ------------
d:\oracle\admin\ora92\udump    ora92_ora_3164.trc        29/04/2009 18:32:00   448,045,831
d:\oracle\admin\ora92\udump    ora92_ora_4532_lit.trc    19/10/2007 18:14:00        10,799
d:\oracle\admin\ora92\udump    ora92_ora_4364_sctx.trc   19/10/2007 18:23:00        10,536
d:\oracle\admin\ora92\udump    ora92_ora_4708_bvcs.trc   19/10/2007 18:28:00        10,513
d:\oracle\admin\ora92\udump    ora92_ora_3340_bv.trc     19/10/2007 18:14:00         9,802
d:\oracle\admin\ora92\udump    ora92_ora_5156.trc        28/04/2009 18:00:00         1,784

6 rows selected.

We can see that this is a simple and relatively secure method for listing directory files with a preprocessor. To make this more robust, we would need to add some exception handling and an application lock around the FILES_XT access. We could also pass in a collection of directory files and have multiple locations set and listed in one query, but enhancements like this can be an exercise for the reader!

further reading

For more information on the external table preprocessor, read the ORACLE_LOADER Access Driver documentation for 11g Release 2.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, October 2009

Back to Top