DBA Hub

📋Steps in this guide1/6

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.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

Setup

We connect to a privileged user, and create a new test user. Notice the , and privileges granted to the test user. We also make sure the and 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 command shows us the contents of the file once it's been written.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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;

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"
$
2

Create Scheduler Objects

We connect to the new test user. We create a credential, which is the operating system user that will be performing the external action. We create a scheduler program that runs the script and defines an argument to be passed to it.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CONN testuser1/testuser1@//localhost:1521/pdb1

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

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;
/
3

Create Supporting Objects

We need the package to split a CLOB into an array. We get this package from the following script. We create the package specification, which will be doing the work of getting the file list and presenting it as a pipelined table function. We create the package body.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
SQL> @https://oracle-base.com/dba/miscellaneous/string_api.sql

Package created.

Package body created.

SQL>

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;
/

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;
/
4

Test It

We can query the files using the pipelined table function. We can limit the output using a clause in the normal way. These queries can be used in PL/SQL.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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>

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>
5

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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%';
6

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...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!