List Files in a Directory From PL/SQL and SQL : External Table

This article shows how to list files in a directory on the database server using an external table. This article is based on this great article by Adrian Billigton, but it's been adjusted to suit my needs.

Setup
Create External Table
Test It
Using Views
Pros and Cons

Setup

We connect to a privileged user, and create a new test user.

CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA
DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO testuser1;

We create three directories to handle the processing of our directory listing functionality.

"/u01/fs_list/logs" : A directory used by the external table to write logs. This needs read-write permissions.
"/u01/fs_list/script" : A directory to hold a pre-processor script used to list the files in a directory. This needs read-execute permissions.
"/u01/fs_list/control" : A directory to hold files to control which directories can be listed. This needs read permissions.

We create the directories using the following commands.

mkdir -p /u01/fs_list/logs
mkdir -p /u01/fs_list/script
mkdir -p /u01/fs_list/control

We create the Oracle directory objects associated with these physical directories, granting the relevant permissions to our test user.

CREATE OR REPLACE DIRECTORY fs_list_logs_dir AS '/u01/fs_list/logs/';
GRANT READ, WRITE ON DIRECTORY fs_list_logs_dir TO testuser1;
CREATE OR REPLACE DIRECTORY fs_list_script_dir AS '/u01/fs_list/script/';
GRANT READ, EXECUTE ON DIRECTORY fs_list_script_dir TO testuser1;
CREATE OR REPLACE DIRECTORY fs_list_control_dir AS '/u01/fs_list/control/';
GRANT READ ON DIRECTORY fs_list_control_dir TO testuser1;

We create the pre-processor script with the following commands. The script lists the files in the directory provided by the external table LOCATION clause. It specifies the date format in a more useful form than the default format. The second cat command shows us the contents of the file once it's been written.

cat> /u01/fs_list/script/list_directory.sh <<EOF
#!/bin/bash
/usr/bin/ls -l --time-style=+"%Y-%m-%d:%H:%M:%S" "\$(/usr/bin/cat \1ドル)"
EOF
chmod u+x /u01/fs_list/script/list_directory.sh
cat /u01/fs_list/script/list_directory.sh
#!/bin/bash
/usr/bin/ls -l --time-style=+"%Y-%m-%d:%H:%M:%S" "$(/usr/bin/cat 1ドル)"
$

For each directory we want to list files in, we create a separate file control file that contains the path of interest. We'll just create two files. One for the ORACLE_BASE location and one for the trace file location.

cat> /u01/fs_list/control/oraclebase.txt <<EOF
/u01/app/oracle
EOF
cat> /u01/fs_list/control/trace.txt <<EOF
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace
EOF

Create External Table

We connect to our test user and create an external table. Notice the use of the directory objects in bold.

CONN testuser1/testuser1@//localhost:1521/pdb1
DROP TABLE list_directory_ext;
CREATE TABLE list_directory_ext (
 file_name VARCHAR2(200),
 file_permissions VARCHAR2(11),
 file_hardlinks NUMBER,
 file_owner VARCHAR2(32),
 file_group VARCHAR2(32),
 file_size NUMBER,
 file_datetime DATE
)
ORGANIZATION EXTERNAL (
 TYPE ORACLE_LOADER
 DEFAULT DIRECTORY fs_list_logs_dir
 ACCESS PARAMETERS (
 RECORDS DELIMITED BY NEWLINE
 PREPROCESSOR fs_list_script_dir:'list_directory.sh'
 FIELDS TERMINATED BY WHITESPACE
 (
 file_permissions,
 file_hardlinks,
 file_owner,
 file_group,
 file_size,
 file_datetime DATE 'YYYY-MM-DD:HH24:MI:SS',
 file_name
 )
 )
 LOCATION (fs_list_control_dir:'oraclebase.txt')
)
REJECT LIMIT UNLIMITED;

Test It

We can query the external table to get the list of files and directories in default location, which is pointed to by the "oraclebase.txt" file.

ALTER SESSION SET nls_date_format='DD-MON-YYYY:HH24:MI:SS';
SET LINESIZE 200
COLUMN file_name FORMAT A30
COLUMN file_owner FORMAT A10
COLUMN file_group FORMAT A10
SELECT file_name,
 file_permissions,
 file_hardlinks,
 file_owner,
 file_group,
 file_size,
 file_datetime
FROM list_directory_ext;
FILE_NAME FILE_PERMIS FILE_HARDLINKS FILE_OWNER FILE_GROUP FILE_SIZE FILE_DATETIME
------------------------------ ----------- -------------- ---------- ---------- ---------- --------------------
admin drwxr-x---. 3 oracle oinstall 18 30-JUN-.&checktime(2020,11,23,':'):07
audit drwxr-x---. 3 oracle oinstall 18 30-JUN-.&checktime(2020,11,25,':'):41
cfgtoollogs drwxr-x---. 4 oracle oinstall 34 30-JUN-.&checktime(2020,11,25,':'):42
checkpoints drwxr-xr-x. 2 oracle oinstall 6 30-JUN-.&checktime(2020,11,22,':'):43
diag drwxrwxr-x. 23 oracle oinstall 280 30-JUN-.&checktime(2020,11,22,':'):44
product drwxr-xr-x. 3 oracle oinstall 20 30-JUN-.&checktime(2020,11,19,':'):02
6 rows selected.
SQL>

We can list the files and directories in the trace location by using the "trace.txt" file. We can do this by altering the external table LOCATION clause. We set it back to the default location once we are done.

ALTER TABLE list_directory_ext LOCATION (fs_list_control_dir:'trace.txt');
SELECT file_name,
 file_permissions,
 file_hardlinks,
 file_owner,
 file_group,
 file_size,
 file_datetime
FROM list_directory_ext;
FILE_NAME FILE_PERMIS FILE_HARDLINKS FILE_OWNER FILE_GROUP FILE_SIZE FILE_DATETIME
------------------------------ ----------- -------------- ---------- ---------- ---------- --------------------
alert_cdb1.log -rw-r-----. 1 oracle oinstall 172573 30-JUN-.&checktime(2020,17,48,':'):43
cdb1_dbrm_15137.trc -rw-r-----. 1 oracle oinstall 1061 30-JUN-.&checktime(2020,11,23,':'):16
cdb1_dbrm_15137.trm -rw-r-----. 1 oracle oinstall 912 30-JUN-.&checktime(2020,11,23,':'):16
... deleted for brevity ...
cdb1_vktm_17026.trm -rw-r-----. 1 oracle oinstall 952 30-JUN-.&checktime(2020,11,56,':'):17
cdb1_vktm_17896.trc -rw-r-----. 1 oracle oinstall 1230 30-JUN-.&checktime(2020,11,56,':'):37
cdb1_vktm_17896.trm -rw-r-----. 1 oracle oinstall 930 30-JUN-.&checktime(2020,11,56,':'):37
423 rows selected.
SQL>
ALTER TABLE list_directory_ext LOCATION (fs_list_control_dir:'oraclebase.txt');

Alternatively, from 18c onward we can modify the external table location directly in the SQL.

SELECT file_name,
 file_permissions,
 file_hardlinks,
 file_owner,
 file_group,
 file_size,
 file_datetime
FROM list_directory_ext EXTERNAL MODIFY (LOCATION (fs_list_control_dir:'trace.txt'));
FILE_NAME FILE_PERMIS FILE_HARDLINKS FILE_OWNER FILE_GROUP FILE_SIZE FILE_DATETIME
------------------------------ ----------- -------------- ---------- ---------- ---------- --------------------
alert_cdb1.log -rw-r-----. 1 oracle oinstall 172573 30-JUN-.&checktime(2020,17,48,':'):43
cdb1_dbrm_15137.trc -rw-r-----. 1 oracle oinstall 1061 30-JUN-.&checktime(2020,11,23,':'):16
cdb1_dbrm_15137.trm -rw-r-----. 1 oracle oinstall 912 30-JUN-.&checktime(2020,11,23,':'):16
... deleted for brevity ...
cdb1_vktm_17026.trm -rw-r-----. 1 oracle oinstall 952 30-JUN-.&checktime(2020,11,56,':'):17
cdb1_vktm_17896.trc -rw-r-----. 1 oracle oinstall 1230 30-JUN-.&checktime(2020,11,56,':'):37
cdb1_vktm_17896.trm -rw-r-----. 1 oracle oinstall 930 30-JUN-.&checktime(2020,11,56,':'):37
423 rows selected.
SQL>

Using Views

You can hide the internal workings by creating views over the external table. The following views sit in front of the external table, setting the appropriate location file and filtering out directories, so we only see files listed.

CREATE OR REPLACE VIEW oraclebase_files_v AS
SELECT *
FROM list_directory_ext EXTERNAL MODIFY (LOCATION (fs_list_control_dir:'oraclebase.txt'))
WHERE file_permissions NOT LIKE 'd%';
CREATE OR REPLACE VIEW trace_files_v AS
SELECT *
FROM list_directory_ext EXTERNAL MODIFY (LOCATION (fs_list_control_dir:'trace.txt'))
WHERE file_permissions NOT LIKE 'd%';

Pros and Cons

Pros of this method are.

Doesn't require too much initial setup.
Doesn't give additional access to the files. It just lists them.
Has a control mechanism for what directories can be accessed.
Can list files from any directory on the DB server that is accessible to the "oracle" OS user.
Gives addition details about the files, not just the file names. We can tailor this further by altering the pre-processor script if we want.

Cons of this method are.

Does require some initial setup on the file system.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)