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

Home » Articles » Misc » Here

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

This article shows how to list files in a directory on the database server using a Java in the database. This is a cut-down version of the general file API described in this article.

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 TYPE, CREATE VIEW TO testuser1;

We grant the relevant Java privileges to the test user to allow it to access the file system. Notice we are only granting read access here. For the general file API we need to grant additional privieleges.

EXEC DBMS_JAVA.grant_permission('TESTUSER1', 'java.io.FilePermission', '<>', 'read');
EXEC DBMS_JAVA.grant_permission('TESTUSER1', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
GRANT JAVAUSERPRIV TO TESTUSER1;

Create the Java Class, Call Specification and Pipelined Table Function

We connect to the new test user.

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

We create the FileListHandler Java class.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileListHandler" AS
import java.lang.*;
import java.util.*;
import java.io.*;

public class FileListHandler
{
  public static String list (String path) {
    String list = "";
    File myFile = new File (path);
    String[] arrayList = myFile.list();
    
    Arrays.sort(arrayList, String.CASE_INSENSITIVE_ORDER);
    
    for (int i=0; i < arrayList.length; i++) {
      // Prevent directory listing expanding if we will blow VARCHAR2 limit.
      if ((list.length() + arrayList[i].length() + 1) > 32767)
        break;
        
      if (!list.equals(""))
        list += "," + arrayList[i];
      else
        list += arrayList[i];
    }
    return list;
  }
};
/
show errors java source "FileListHandler"

We create a call specification for the Java class. This could be a standalone function, but I prefer to use a package.

CREATE OR REPLACE PACKAGE file_list_api AS

FUNCTION list (p_path  IN  VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'FileListHandler.list (java.lang.String) return java.lang.String';

END file_list_api;
/
SHOW ERRORS

We create a pipelined table function to allow convenient access to the FILE_LIST_API package from SQL. We split the returned comma-separated list into an array. There are a number of ways to do that, but this method uses the APEX_STRING package.

CREATE OR REPLACE TYPE t_varchar2_arr AS TABLE OF VARCHAR2(500);
/

CREATE OR REPLACE FUNCTION get_files (p_dir IN VARCHAR2)
  RETURN t_varchar2_arr PIPELINED
AS
  l_array  APEX_APPLICATION_GLOBAL.vc_arr2;
  l_string VARCHAR2(32767);
BEGIN
  l_array:= APEX_STRING.string_to_table(FILE_LIST_API.list(p_dir), ',');

  FOR i in 1..l_array.count LOOP
    PIPE ROW(l_array(i));
  END LOOP;
  RETURN;
END;
/

Test It

We can use the LIST function in the FILE_LIST_API package to list files and sub-directories in a directory. Notice the files are presented as a comma-separated list.

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('Output : ' ||  FILE_LIST_API.list ('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'));
END;
/
Output : alert_cdb1.log,cdb1_cjq0_18139.trc,cdb1_cjq0_18139.trm,cdb1_dbrm_15137.trc,cdb1_dbrm_15137.trm ...

... removed for brevity ...

cdb1_vktm_17896.trc,cdb1_vktm_17896.trm


PL/SQL procedure successfully completed.

SQL>

We can split the list into an array. There are a number of ways to do that, but this method uses the APEX_STRING package.

DECLARE
  l_array APEX_APPLICATION_GLOBAL.vc_arr2;
  l_string varchar2(2000);
BEGIN
  l_array:= APEX_STRING.string_to_table(FILE_LIST_API.list ('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'), ',');

  FOR i in 1..l_array.count LOOP
    DBMS_OUTPUT.put_line('Array(' || i || ') : ' || l_array(i));
  END LOOP;
END;
/
Array(1) : alert_cdb1.log
Array(2) : cdb1_cjq0_18139.trc
Array(3) : cdb1_cjq0_18139.trm
Array(4) : cdb1_dbrm_15137.trc

... removed for brevity ...

Array(531) : cdb1_vktm_17026.trm
Array(532) : cdb1_vktm_17896.trc
Array(533) : cdb1_vktm_17896.trm


PL/SQL procedure successfully completed.

SQL>

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

COLUMN file_name FORMAT A30

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

FILE_NAME
------------------------------
alert_cdb1.log
cdb1_cjq0_18139.trc
cdb1_cjq0_18139.trm

... removed for brevity ...

cdb1_vktm_17896.trc
cdb1_vktm_17896.trm

539 rows selected.

SQL>

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

SELECT column_value AS file_name
FROM   TABLE(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(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(get_files('/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace'))
WHERE  column_value LIKE 'alert%';

Pros and Cons

Pros of this method are.

Cons of this method are.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.