FILE_LIST_API.list - List Files in a Directory From PL/SQL and SQL using Java class FileListHandler

References:

• oracle-base.com - List Files in a Directory From PL/SQL and SQL: Java

I chose a variation suggested by Christian Antognini which passes back an array:

Java class FileListHandler

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileListHandler" AS
import java.io.File;
import java.lang.Exception;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Array;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
 
public class FileListHandler
{
  public static Array list (String path) throws Exception {
    Path directory = Paths.get(path);
    if (!Files.isDirectory(directory))
      throw new Exception("path argument does not reference a directory (" + path + ")");
 
    File[] files = directory.toFile().listFiles();
    OracleConnection connection = (OracleConnection)(new OracleDriver()).defaultConnection();
    return connection.createOracleArray("T_VARCHAR2_ARR", files);
  }
};
/

PL/SQL package file_list_api with function list

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


CREATE OR REPLACE PACKAGE file_list_api AS

FUNCTION list (p_path  IN  VARCHAR2) RETURN t_varchar2_arr
AS LANGUAGE JAVA
NAME 'FileListHandler.list (java.lang.String) return java.sql.Array';
 
END file_list_api;
/

Example of using:

SELECT * FROM table(FILE_LIST_API.list ('/u01/app/oracle/admin/orcl/adump/'));

Another implementing of FILE_LIST_API.list PL/SQL function in fDelete PL/SQL function as wrapper of Java JDelete.delete method for delete files from OS