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-.&checktime(2020,21,23,':'):13
cdb1_dbrm_15138.trc -rw-r-----. 1 oracle oinstall 1061 03-JUL-.&checktime(2020,13,59,':'):10
cdb1_dbrm_15138.trm -rw-r-----. 1 oracle oinstall 912 03-JUL-.&checktime(2020,13,59,':'):10
... removed for brevity ...
cdb1_vktm_17910.trc -rw-r-----. 1 oracle oinstall 1230 03-JUL-.&checktime(2020,14,29,':'):08
cdb1_vktm_17910.trm -rw-r-----. 1 oracle oinstall 930 03-JUL-.&checktime(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-.&checktime(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.

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