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.
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 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:
Hope this helps. Regards Tim...