8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

Related articles.

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.

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-2020:11:23:07
audit                          drwxr-x---.              3 oracle     oinstall           18 30-JUN-2020:11:25:41
cfgtoollogs                    drwxr-x---.              4 oracle     oinstall           34 30-JUN-2020:11:25:42
checkpoints                    drwxr-xr-x.              2 oracle     oinstall            6 30-JUN-2020:11:22:43
diag                           drwxrwxr-x.             23 oracle     oinstall          280 30-JUN-2020:11:22:44
product                        drwxr-xr-x.              3 oracle     oinstall           20 30-JUN-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-2020:17:48:43
cdb1_dbrm_15137.trc            -rw-r-----.              1 oracle     oinstall         1061 30-JUN-2020:11:23:16
cdb1_dbrm_15137.trm            -rw-r-----.              1 oracle     oinstall          912 30-JUN-2020:11:23:16

... deleted for brevity ...

cdb1_vktm_17026.trm            -rw-r-----.              1 oracle     oinstall          952 30-JUN-2020:11:56:17
cdb1_vktm_17896.trc            -rw-r-----.              1 oracle     oinstall         1230 30-JUN-2020:11:56:37
cdb1_vktm_17896.trm            -rw-r-----.              1 oracle     oinstall          930 30-JUN-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-2020:17:48:43
cdb1_dbrm_15137.trc            -rw-r-----.              1 oracle     oinstall         1061 30-JUN-2020:11:23:16
cdb1_dbrm_15137.trm            -rw-r-----.              1 oracle     oinstall          912 30-JUN-2020:11:23:16

... deleted for brevity ...

cdb1_vktm_17026.trm            -rw-r-----.              1 oracle     oinstall          952 30-JUN-2020:11:56:17
cdb1_vktm_17896.trc            -rw-r-----.              1 oracle     oinstall         1230 30-JUN-2020:11:56:37
cdb1_vktm_17896.trm            -rw-r-----.              1 oracle     oinstall          930 30-JUN-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.

Cons of this method are.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.