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_BACKUP_RESTORE

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

Setup
Create Pipelined Table Function
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 VIEW TO testuser1;

Create Pipelined Table Function

Whilst still connected to the privileged user, we create a table type and a pipelined table function using the DBMS_BACKUP_RESTORE package. The call to the SEARCHFILES procedure sets the starting point for the search. We query the file names from the X$KRBMSFT table. By default this does a recursive search, so we have to limit the output to files in the requested directory. Finally, we pipe out the file names, removing the full path leading to it.

We can't grant privileges on the X$ tables, so this pipelined table function must be owned by the SYS user.

CREATE OR REPLACE TYPE sys.ob_varchar2_arr AS TABLE OF VARCHAR2(500);
/

CREATE OR REPLACE FUNCTION sys.ob_get_files (p_pattern        IN VARCHAR2,
                                             p_file_separator IN VARCHAR2 := '/')
  RETURN sys.ob_varchar2_arr PIPELINED
AS
  l_pattern VARCHAR2(32767);
  l_ns      VARCHAR2(32767);
BEGIN
  -- Make sure the pattern ends with the file separator.
  l_pattern := RTRIM(p_pattern, p_file_separator) || p_file_separator;
   
  sys.DBMS_BACKUP_RESTORE.searchfiles(
    pattern => l_pattern,
    ns      => l_ns,
    onlyfnm => TRUE,
    normfnm => TRUE);

  -- Pull back all files directly under specified directory.
  -- WHERE filter removes recursion.
  FOR cur_rec IN (SELECT fname_krbmsft
                  FROM   sys.x$krbmsft
                  WHERE  INSTR(SUBSTR(fname_krbmsft, LENGTH(l_pattern)+1), p_file_separator) = 0)
  LOOP
    -- Display the file name without the preceding path.
    PIPE ROW(SUBSTR(cur_rec.fname_krbmsft, LENGTH(l_pattern)+1));
  END LOOP;
  RETURN;
END;
/

We make sure our test user have access to the pipelined table function and the table type.

GRANT EXECUTE ON sys.ob_varchar2_arr TO testuser1;
GRANT EXECUTE ON sys.ob_get_files TO testuser1;

Test It

We connect to the test user and run a query, passing the directory of interest. It doesn't matter if we use final "/" or not, as the code always adds one.

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

COLUMN file_name FORMAT A30

SELECT column_value AS file_name
FROM   TABLE(sys.ob_get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'));

FILE_NAME
------------------------------
cdb1_ora_15109.trc
cdb1_ora_15109.trm
alert_cdb1.log

... removed for brevity ...

cdb1_ora_20934.trm
cdb1_m004_20954.trc
cdb1_m004_20954.trm

481 rows selected.

SQL>

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

SELECT column_value AS file_name
FROM   TABLE(sys.ob_get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'))
WHERE  column_value LIKE 'alert%';

FILE_NAME
------------------------------
alert_cdb1.log

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 column_value AS file_name
FROM   TABLE(sys.ob_get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'));

CREATE OR REPLACE VIEW alert_log_v AS
SELECT column_value AS file_name
FROM   TABLE(sys.ob_get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'))
WHERE  column_value LIKE 'alert%';

Pros and Cons

Pros of this method are.

Doesn't give additional access to the files. It just lists them.
Can list files from any directory on the DB server that is accessible to the "oracle" OS user.

Cons of this method are.

We have to create objects in the SYS schema. This is not a great idea.
The DBMS_BACKUP_RESTORE package is not documented, so it is not officially supported for our use. It's an internal package used by the database.
We don't get any additional details about the files. Just the file name.
We have no control over the directories that are searched unless we code in some additional checks.

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.