List Contents of Directory from Oracle SQL

One of my colleagues asked me how to list all content of directory from Oracle SQL query.
I think UTL_FILE built-in package allow this, but I discovered It doesn't support this.

So I decide to develop java class for listing contents of directory, then use it in Oracle Database.

I will list steps I will do briefly 
1-Create collection to store list of files in it.
2-Develop Java Class for List contents of Directory
3-Add Java Class to Database
4-Create PLSQL wrapper function to Java method
5-Grant Read on Directory to Java
6-Call PLSQL Function

1-Create collection to store list of files in it.

I will create table of varchar2 to hold files of directory.

CREATE OR REPLACE TYPE VARCHAR2T AS TABLE OF VARCHAR2(4000);

2-Develop Java Class for List contents of Directory

 import java.io.File;  
 import java.sql.Connection;  
 import java.sql.SQLException;  
 import oracle.sql.ARRAY;  
 import oracle.sql.ArrayDescriptor;  
 import java.util.ArrayList;  
 import oracle.jdbc.OracleDriver;  
 public class DirectoryContent {  
   public static ArrayList getDirectoryContent(String directory, String searchInSubDir) {  
     File file = new File(directory);  
     File[] contentOfDirectory = file.listFiles();  
     ArrayList content = new ArrayList();  
     if (contentOfDirectory != null) {  
       for (int i = 0; i < contentOfDirectory.length; i++) {  
         if (contentOfDirectory[i].isFile()) {  
           content.add(directory + "\\" + contentOfDirectory[i].getName());  
         } else if (searchInSubDir == "Y") {  
           ArrayList recursive =  
             getDirectoryContent(directory + "\\" + contentOfDirectory[i].getName(), searchInSubDir);  
           for (int indx = 0; indx < recursive.size(); indx++) {  
             String recursiveData = (String)recursive.get(indx);  
             content.add(recursiveData);  
           }  
         }  
       }  
     }  
     return content;  
   }  
   public static ARRAY getDirectoryContentSql(String directory, String searchInSubDir) throws SQLException {  
     Connection conn = new OracleDriver().defaultConnection();  
     ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("VARCHAR2T", conn);  
     ArrayList result = getDirectoryContent(directory, searchInSubDir);  
     ARRAY array = new ARRAY(arrayDescriptor, conn, result.toArray());  
     return array;  
   }  
   public static void main(String[] args) throws SQLException {  
     Object[] result = DirectoryContent.getDirectoryContent("D:\\Mahmoud", "Y").toArray();  
     for (int i = 0; i < result.length; i++) {  
       String string = result[i].toString();  
       System.out.println(string);  
     }  
   }  
 }  

3-Add Java Class to Database

I will Add Java Class to Database using below command

 CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DirectoryContent"  
   AS import java.io.File;  
 import java.sql.Connection;  
 import java.sql.SQLException;  
 import oracle.sql.ARRAY;  
 import oracle.sql.ArrayDescriptor;  
 import java.util.ArrayList;  
 import oracle.jdbc.OracleDriver;  
 public class DirectoryContent {  
   public static ArrayList getDirectoryContent(String directory, String searchInSubDir) {  
     File file = new File(directory);  
     File[] contentOfDirectory = file.listFiles();  
     ArrayList content = new ArrayList();  
     if (contentOfDirectory != null) {  
       for (int i = 0; i < contentOfDirectory.length; i++) {  
         if (contentOfDirectory[i].isFile()) {  
           content.add(directory + "\\" + contentOfDirectory[i].getName());  
         } else if (searchInSubDir == "Y") {  
           ArrayList recursive =  
             getDirectoryContent(directory + "\\" + contentOfDirectory[i].getName(), searchInSubDir);  
           for (int indx = 0; indx < recursive.size(); indx++) {  
             String recursiveData = (String)recursive.get(indx);  
             content.add(recursiveData);  
           }  
         }  
       }  
     }  
     return content;  
   }  
   public static ARRAY getDirectoryContentSql(String directory, String searchInSubDir) throws SQLException {  
     Connection conn = new OracleDriver().defaultConnection();  
     ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("VARCHAR2T", conn);  
     ArrayList result = getDirectoryContent(directory, searchInSubDir);  
     ARRAY array = new ARRAY(arrayDescriptor, conn, result.toArray());  
     return array;  
   }  
   public static void main(String[] args) throws SQLException {  
     Object[] result = DirectoryContent.getDirectoryContent("D:\\Mahmoud", "Y").toArray();  
     for (int i = 0; i < result.length; i++) {  
       String string = result[i].toString();  
       System.out.println(string);  
     }  
   }  
 }  
 /  

4-Create PLSQL wrapper function to Java method

Now my java class is uploaded in database, So I should create PLSQL Function that call Java method and return VARCHAR2T type

CREATE OR REPLACE FUNCTION get_directory_content (
   in_directory   IN VARCHAR2,
   in_recursive   IN VARCHAR2)
   RETURN varchar2t
AS
   LANGUAGE JAVA
   NAME 'DirectoryContent.getDirectoryContentSql (java.lang.String,java.lang.String) return oracle.sql.ARRAY ';
/

5-Grant Read on Directory to Java

You must have read permission on directory which you will list its contents.
I can add permission to database user who will execute function using below script.
You must have DBA privilege to execute below script

BEGIN
   DBMS_JAVA.grant_permission ('SCOTT',
                               'SYS:java.io.FilePermission',
                               'C:\Oracle\Middleware',
                               'read');
   DBMS_JAVA.grant_permission ('SCOTT',
                               'SYS:java.io.FilePermission',
                               'C:\Oracle\Middleware\*',
                               'read');
END;

Note : There are types of permission that sometimes you need to granted it when using java in Oracle Database
java.util.PropertyPermission
java.io.SerializablePermission
java.io.FilePermission
java.net.NetPermission
java.net.SocketPermission
java.lang.RuntimePermission
java.lang.reflect.ReflectPermission
java.security.SecurityPermission
oracle.aurora.rdbms.security.PolicyTablePermission
oracle.aurora.security.JServerPermission

6-Call PLSQL Function

Now let's test my function and assure it works truly.
SELECT * FROM TABLE (get_directory_content ('C:\Oracle\Middleware', 'Y'));

The result as below

Conclusion

Sometimes You want to do function that doesn't supported by oracle directly, In this case take Java road and develop what you need but you should take in consideration that every database version support specific version of Java listed below

Oracle8i Database (8.1.5)                JDK 1.1.6
Oracle8i Database (8.1.6 or later)       JDK 1.2
Oracle9i Database                        J2SE 1.3
Oracle Database 10g Release 1            J2SE 1.4.1
Oracle Database 10g Release 2            J2SE 1.4.2

Thanks
Mahmoud Ahmed El-Sayed