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 184.108.40.206.0
Oracle SQL Developer 220.127.116.11
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 /
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
4 thoughts on “Listing files from a Linux directory from within Oracle SQL or PL/SQL in 11g”
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!
I take back my last question/ Found the answer right inside this article. Thaks anyhow!
In my case I had to modify it ; following worked for me.
below example splits the row with space and does not specify the field limit since you never know what is coming in username, group, file size, file name and data time of file.
— DDL for Table HOME_DIRECTORY
CREATE TABLE “CPSRPTL”.”HOME_DIRECTORY”
( “FILERECORD” VARCHAR2(15 BYTE),
“FLINK” VARCHAR2(2 BYTE),
“FOWNER” VARCHAR2(6 BYTE),
“FGROUP” VARCHAR2(8 BYTE),
“FSIZE” VARCHAR2(32 BYTE),
“FDATE_PART1” VARCHAR2(16 BYTE),
“FDATE_PART2” VARCHAR2(16 BYTE),
“FDATE_YEAR_OR_TIME” VARCHAR2(16 BYTE),
“FNAME” VARCHAR2(255 BYTE)
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY “SCRIPT_TEMP_DIR”
( records delimited by newline
fields terminated by whitespace
REJECT LIMIT UNLIMITED ;
i love External tables