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
Oracle SQL Developer

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

CREATE DIRECTORY PREPROCESSOR_DIRECTORY AS '/home/oracle/preprocessor_scripts'

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 (The file owner in my example is oracle)

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)
       records delimited by newline
       preprocessor PREPROCESSOR_DIRECTORY:''
       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 ('')

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, 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.

  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:

   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:

  FROM files_view fv



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


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

2 thoughts on “Listing files from a Linux directory from within Oracle SQL or PL/SQL in 11g

  1. Shimon Batashvili says:

    First of all I must tell you that I loved your post. My question is about creating such table and base it on a Microsoft environment, i.e., will it work with .bat or .cmd batch files?

    Thanks in advance!
    Shimon B.


  2. Shimon Batashvili says:

    I take back my last question/ Found the answer right inside this article. Thaks anyhow!


    Shimon B.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: