In this article I will describe a method which enables you to list the contents of a Linux directory from within Oracle using the 11gR2 Preprocessor feature of Oracle External Tables.

The example that follows was built using the Oracle Developer Days Environment and used the following versions:

Oracle Linux running via Oracle Virtual Box
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Oracle SQL Developer 3.2.20.10

After logging into a database session as a SYSDBA user, run the following commands:

CREATE DIRECTORY PREPROCESSOR_DIRECTORY AS '/home/oracle/preprocessor_scripts'
/
GRANT EXECUTE ON DIRECTORY PREPROCESSOR_DIRECTORY TO HR
/

The first command creates a new directory object, called PREPROCESSOR_DIRECTORY which points to a location on the file system.

The next command grants the execute privilege on the directory to the HR user. The execute privilege allows the HR to run scripts in this folder.

After switching to the file system and to the location specified by the directory object, create the following bash script and save it as list_files.sh (The file owner in my example is oracle)

#!/bin/bash
cd /home/oracle
/bin/ls -l

As you can see the script changes directory to the home directory and then lists the files.

The next step is to start a new database session as the HR user and create the following External Table:

CREATE TABLE home_directory
(
   fpermissions   VARCHAR2(10),
   flink          VARCHAR2(2),
   fowner         VARCHAR2(6),
   fgroup         VARCHAR2(6),
   fsize          VARCHAR2(100),
   fdate          VARCHAR2(100),
   fname          VARCHAR2(255)
)
   ORGANIZATION EXTERNAL
   (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY PREPROCESSOR_DIRECTORY
      ACCESS PARAMETERS
      (
       records delimited by newline
       preprocessor PREPROCESSOR_DIRECTORY:'list_files.sh'
       fields terminated by whitespace
       (
          fpermissions   position(1:10),
          flink          position(12:14),
          fowner         position(15:21),
          fgroup         position(22:28),
          fsize          position(28:37),
          fdate          position(37:49),
          fname          position(50:305)
       )
      )
   LOCATION ('list_files.sh')
   )
REJECT LIMIT UNLIMITED
/

The majority of the code shown above is the standard for creating an Oracle External Table. I will highlight the key points:

Line 18 shows the preprocessor command that calls the bash script, list_files.sh created in the preceding step. In order for this command to run, the user requires the execute privilege on the PREPROCESSOR_DIRECTORY object (which was granted here).

Lines 21 – 27 slices up the output from the bash script into the columns of the External Table.

Line 30 The location clause; Although not used in this example, this clause is mandatory, so I have pointed to the same bash script.

With the External Table in place, you can query to check what the output look like.

SELECT *
  FROM home_directory
/

Running this SQL on my environment produces the following output:

Whilst this proves you can now query the Linux directory from SQL, the output is relatively “raw”. The first line returned shows the total number of blocks used by files included in the directory listing and there are directories and files mixed in together.

You could go back and amend the External Table definition to exclude information that is not required, I prefer to clean up the output by creating a view of the data that I want to see. I like this method because when the requirements change and I need to see different information from the home directory I can simply create another view that returns that information.

Here is a view that just returns the files from the home directory:

CREATE OR REPLACE VIEW files_view
AS
   SELECT hd.fpermissions file_permissions,
          hd.fowner       file_owner,
          hd.fgroup       file_group,
          hd.fsize        file_size,
          hd.fdate        file_date,
          hd.fname        file_name
     FROM home_directory hd
    WHERE hd.fpermissions NOT LIKE 'total%'
      AND hd.fpermissions NOT LIKE 'd%'
/

Line 10 Excludes the total number of blocks row

Line 11 Excludes directories

With the view created I can now get query it to see just the files in the home directory:

SELECT fv.*
  FROM files_view fv
/

Summary

In this article I have shown a method available that allows you to obtain a listing of a Linux directory.

Acknowledgements

This post was inspired by Adrian Billington’s comprehensive post on Listing Files with the External table preprocessor