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

List Files in a Directory From PL/SQL and SQL : DBMS_SCHEDULER

This article shows how to list files in a directory on the database server using the DBMS_SCHEDULER package.

Setup
Create Scheduler Objects
Create Supporting Objects
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 PROCEDURE, CREATE VIEW TO testuser1;
GRANT CREATE JOB, CREATE EXTERNAL JOB, CREATE CREDENTIAL TO testuser1;
GRANT SELECT ON user_scheduler_job_run_details TO testuser1;
GRANT SELECT ON user_scheduler_running_jobs TO testuser1;

Notice the CREATE JOB, CREATE EXTERNAL JOB and CREATE CREDENTIAL privileges granted to the test user. We also make sure the USER_SCHEDULER_JOB_RUN_DETAILS and USER_SCHEDULER_RUNNING_JOBS views have select granted directly to the test user.

We create a script with the following commands. The script lists the files in the directory provided by the scheduler job. 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.

mkdir -p /u01/fs_list/script

cat > /u01/fs_list/script/list_directory.sh <<EOF
#!/bin/bash
/usr/bin/ls -l --time-style=+"%Y-%m-%d:%H:%M:%S" "\$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" "$1"
$

Create Scheduler Objects

We connect to the new test user.

CONN testuser1/testuser1@//localhost:1521/pdb1

We create a credential, which is the operating system user that will be performing the external action.

BEGIN
  DBMS_CREDENTIAL.create_credential(
    credential_name => 'ORACLE_CREDENTIAL',
    username        => 'oracle',
    password        => 'oracle');
END;
/

We create a scheduler program that runs the script and defines an argument to be passed to it.

BEGIN
  DBMS_SCHEDULER.create_program (
    program_name        => 'file_list_prog',
    program_type        => 'EXECUTABLE',
    program_action      => '/u01/fs_list/script/list_directory.sh',
    number_of_arguments => 1,
    enabled             => FALSE,
    comments            => 'Program to list files in a directory.');

  DBMS_SCHEDULER.define_program_argument (
    program_name      => 'file_list_prog',
    argument_name     => 'path',
    argument_position => 1,
    argument_type     => 'VARCHAR2',
    default_value     => '/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace');

  DBMS_SCHEDULER.enable (name => 'file_list_prog');
END;
/

Create Supporting Objects

We need the STRING_API package to split a CLOB into an array. We get this package from the following script.

SQL> @https://oracle-base.com/dba/miscellaneous/string_api.sql

Package created.

Package body created.

SQL>

We create the FILE_API package specification, which will be doing the work of getting the file list and presenting it as a pipelined table function.

CREATE OR REPLACE PACKAGE file_api AS

TYPE t_row IS RECORD (
  file_permissions VARCHAR2(11),
  file_hardlinks   NUMBER,
  file_owner       VARCHAR2(32),
  file_group       VARCHAR2(32),
  file_size        NUMBER,
  file_datetime    DATE,
  file_name        VARCHAR2(200)     
);

TYPE t_tab IS TABLE OF t_row;

PROCEDURE get_file_list_clob (p_dir  IN  VARCHAR2,
                              p_clob OUT NOCOPY CLOB);

FUNCTION get_files (p_dir IN VARCHAR2)
  RETURN t_tab PIPELINED;

END file_api;
/

We create the FILE_API package body.

CREATE OR REPLACE PACKAGE BODY file_api AS

PROCEDURE get_file_list_clob (p_dir  IN  VARCHAR2,
                              p_clob OUT NOCOPY CLOB)
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_job_name  VARCHAR2(30);
  l_blob      BLOB;
  l_temp      NUMBER;

  l_dest_offset  PLS_INTEGER := 1;
  l_src_offset   PLS_INTEGER := 1;
  l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
  l_warning      PLS_INTEGER;
BEGIN
  -- Get a unique job name.
  l_job_name := DBMS_SCHEDULER.generate_job_name;

  -- Create a job to list the files.
  DBMS_SCHEDULER.create_job (
    job_name      => l_job_name,
    program_name  => 'file_list_prog',
    enabled       => FALSE,
    auto_drop     => FALSE,
    comments      => 'Job to list files in a directory.');

  DBMS_SCHEDULER.set_attribute(l_job_name, 'credential_name', 'ORACLE_CREDENTIAL');
  DBMS_SCHEDULER.set_job_argument_value(l_job_name, 1, p_dir);
  DBMS_SCHEDULER.enable (l_job_name);

  -- Wait for the job to complete.
  DBMS_SESSION.sleep(0.5);
  LOOP
    BEGIN
      SELECT 1
      INTO   l_temp
      FROM   user_scheduler_running_jobs
      WHERE  job_name = l_job_name;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;
    DBMS_SESSION.sleep(0.5);
  END LOOP;

  -- Get the binary output.  
  SELECT binary_output
  INTO   l_blob
  FROM   user_scheduler_job_run_details
  WHERE  job_name = l_job_name
  ORDER BY log_id DESC
  FETCH FIRST ROW ONLY;

  -- Convert it to a CLOB.
  DBMS_LOB.createTemporary(
    lob_loc => p_clob,
    cache   => TRUE);

  DBMS_LOB.converttoclob(
   dest_lob      => p_clob,
   src_blob      => l_blob,
   amount        => DBMS_LOB.lobmaxsize,
   dest_offset   => l_dest_offset,
   src_offset    => l_src_offset, 
   blob_csid     => DBMS_LOB.default_csid,
   lang_context  => l_lang_context,
   warning       => l_warning);

  -- Get rid of the job.
  DBMS_SCHEDULER.drop_job(l_job_name);
EXCEPTION
  WHEN OTHERS THEN
    p_clob := NULL;
    RAISE;
END get_file_list_clob;


FUNCTION get_files (p_dir IN VARCHAR2)
  RETURN t_tab PIPELINED
AS
  l_clob  CLOB;
  l_rows  string_api.t_split_array;
  l_cols  string_api.t_split_array;
BEGIN
  get_file_list_clob(p_dir, l_clob);

  l_rows := string_api.split_text(l_clob, CHR(10));
  FOR i IN 1 .. l_rows.COUNT LOOP
    BEGIN
      -- Looks stupid, but it stops us from losing spaces in file names. 
      FOR j IN 1 .. 6 LOOP
        l_rows(i) := REGEXP_REPLACE(l_rows(i), '[ ]+', '^', 1, 1);
      END LOOP;

      l_cols := string_api.split_text(l_rows(i), '^');
      PIPE ROW(t_row(l_cols(1),
                     TO_NUMBER(l_cols(2)),
                     l_cols(3),
                     l_cols(4),
                     TO_NUMBER(l_cols(5)),
                     TO_DATE(l_cols(6), 'YYYY-MM-DD:HH24:MI:SS'),
                     l_cols(7)));
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END get_files;

END file_api;
/

Test It

We can query the files using the GET_FILES pipelined table function.

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   TABLE(file_api.get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'));

FILE_NAME                      FILE_PERMIS FILE_HARDLINKS FILE_OWNER FILE_GROUP  FILE_SIZE FILE_DATETIME
------------------------------ ----------- -------------- ---------- ---------- ---------- --------------------
alert_cdb1.log                 -rw-r-----.              1 oracle     oinstall       174275 03-JUL-2020:21:23:13
cdb1_dbrm_15138.trc            -rw-r-----.              1 oracle     oinstall         1061 03-JUL-2020:13:59:10
cdb1_dbrm_15138.trm            -rw-r-----.              1 oracle     oinstall          912 03-JUL-2020:13:59:10

... removed for brevity ...

cdb1_vktm_17910.trc            -rw-r-----.              1 oracle     oinstall         1230 03-JUL-2020:14:29:08
cdb1_vktm_17910.trm            -rw-r-----.              1 oracle     oinstall          930 03-JUL-2020:14:29:08

513 rows selected.

SQL>

We can limit the output using a WHERE clause in the normal way.

SELECT file_name,
       file_permissions,
       file_hardlinks,
       file_owner,
       file_group,
       file_size,
       file_datetime
FROM   TABLE(file_api.get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'))
WHERE  file_name LIKE 'alert%';

FILE_NAME                      FILE_PERMIS FILE_HARDLINKS FILE_OWNER FILE_GROUP  FILE_SIZE FILE_DATETIME
------------------------------ ----------- -------------- ---------- ---------- ---------- --------------------
alert_cdb1.log                 -rw-r-----.              1 oracle     oinstall       174275 03-JUL-2020:21:23:13

SQL>

These queries can be used in PL/SQL.

Using Views

You can hide the internal workings by creating views over the pipelined table function. The following views display all the files in the trace directory, and just the alert log respectively.

CREATE OR REPLACE VIEW trace_files_v AS
SELECT file_name,
       file_permissions,
       file_hardlinks,
       file_owner,
       file_group,
       file_size,
       file_datetime
FROM   TABLE(file_api.get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'));

CREATE OR REPLACE VIEW alert_log_v AS
SELECT file_name,
       file_permissions,
       file_hardlinks,
       file_owner,
       file_group,
       file_size,
       file_datetime
FROM   TABLE(file_api.get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'))
WHERE  file_name LIKE 'alert%';

Pros and Cons

Pros of this method are.

Can list files from any directory on the DB server that the user credential has access to.
Assuming you have created the pipelined table function, it's available from SQL and PL/SQL with no extra work.
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.

There is some setup required on the file system of the database server.
There is a lot of additional code compared to some of the alternative methods, so there is more scope for something to fail.
If the scheduler is disabled, this method stops working.

For more information see:

List Files in a Directory From PL/SQL and SQL : Comparison of Methods

Hope this helps. Regards Tim...

Back to the Top.