List Server Directory Files from Oracle DB using Java Stored Procedure

Overview

This page explains how to register a Java stored procedure inside Oracle Database that lists files from a server directory and returns them as a PL/SQL collection you can query via TABLE(...). Useful for checking output folders, report directories, or log files from the database session.

Java source (create in Oracle)

Run the following in SQL\*Plus / SQL Developer to create a Java source inside the database:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileLister" AS  
import java.io.\*;  
import java.sql.\*;  
import oracle.sql.\*;  
import oracle.jdbc.\*;

public class FileLister {  
 public static ARRAY listFiles(String path) throws SQLException {  
   Connection conn = new OracleDriver().defaultConnection();  
   File folder = new File(path);  
   String\[\] names = folder.list();

   if (names == null) {  
     names = new String\[0\];  
   }

   ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARCHAR2\_LIST", conn);  
   return new ARRAY(desc, conn, names);  
 }  
};  
/  

SQL type (collection)

Create a SQL collection type that holds the file names:

CREATE OR REPLACE TYPE VARCHAR2\_LIST AS TABLE OF VARCHAR2(4000);  
/

This acts like a table of strings and can be used with TABLE() in a SELECT.

PL/SQL wrapper (callable from SQL)

CREATE OR REPLACE FUNCTION LIST\_CLOUD\_DIR\_FILES\_NIDHI (  
 P\_PATH VARCHAR2  
) RETURN VARCHAR2\_LIST  
AS LANGUAGE JAVA  
NAME 'FileLister.listFiles(java.lang.String) return oracle.sql.ARRAY';  
/  

Querying the result

Use the function in SQL to get each file name as a row:

SELECT COLUMN\_VALUE AS FILE\_NAME  
FROM TABLE(LIST\_CLOUD\_DIR\_FILES\_NIDHI('/oraprod/ora11g/MY\_APPL/Appl11g/ta\_outputs'));  

Output will be one row per filename, for example:

Permissions & Security

       
• The Oracle JVM (dbms\_java) must be enabled in the database.
       
• The schema that owns the Java class must have the appropriate Java FilePermission to read directories. Example:
     

-- run as a privileged user (SYS or DBA)  
EXEC DBMS\_JAVA.GRANT\_PERMISSION(  
 'YOUR\_SCHEMA',  
 'SYS:java.io.FilePermission',  
 '/oraprod/ora11g/MY\_APPL/Appl11g/ta\_outputs',  
 'read'  
);  

Grant only the minimum required permission and limit the directory paths. Running Java in the DB introduces security considerations; follow your site's security policy.

Extensions & Improvements

       
• Return file metadata (size, lastModified) by creating a SQL object type with multiple attributes and populating it from Java.
       
• Filter filenames by extension or pattern inside Java before returning.
       
• Implement pagination or limit the number of results to avoid huge result sets.
     

Troubleshooting

       
• If folder.list() returns null, check directory existence and OS permissions for the Oracle process user.
       
• If you get Java security exceptions, confirm DBMS\_JAVA.GRANT\_PERMISSION was executed and that you used the right schema name.
       
• Use SELECT \* FROM USER\_ERRORS WHERE TYPE = 'JAVA SOURCE' AND NAME = 'FILELISTER'; to see Java compile errors.