[PL/SQL] Create and call Java procedure to read directory content

Sometimes even PL/SQL isn't enough; luckily Oracle gives us the ability to store Java functions and treat them as procedures.

Consider the following example: we know some files will be stored in a certain directory without knowing their names beforehand and we'd like to read and process those files from our PL/SQL code. By creating a simple Java function depending on the File class, we can solve our problem quickly and easily.

Firstly, we need to create our Java procedure:

 CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "MyLS" AS  
 import java.io.*;  
 import java.sql.*;  
 public class MyLS  
 {  
   public static void getFilesInDir(String directory) throws SQLException {  
     File path = new File( directory );  
     String[] list = path.list();  
     String element;  
     //for each file inside directory   
     for(int i = 0; i < list.length; i++) {  
       element = list[i];//get its name, complete with extension. It will NOT have the full path attached  
       //call our procedure which will do something with said file, passing its filename as :element  
       #sql {  
         begin  
           myPackage.myProcedure(:element);  
         end;  
       };  
     }  
   }  
 }  

Then we need to create a proxy package based on that procedure:

CREATE OR REPLACE PROCEDURE [user].MyLS (p_directory  IN  VARCHAR2)
AS LANGUAGE JAVA
NAME 'MyLS.getFilesInDir(java.lang.String)';

if our Java procedure is a Java function instead (returns a value) we'll simply add the return declaration (suppose it returns an INTEGER):

CREATE OR REPLACE FUNCTION [user].MyLS (p_directory  IN  VARCHAR2)
AS LANGUAGE JAVA
NAME 'MyLS.getFilesInDir(java.lang.String) RETURN int';

Finally we can call it from our scripts as:

CALL [user].MyLS([directory]);

replacing [directory] with our path ie: 'C:\temp' or '/var/tmp' and [user] with the owner of the object. The [user] part is not mandatory (ie you created a SYNONYM) and you should always make sure that the calling user has the sufficient privileges to invoke the method, if not GRANT him.

NOTE: to effectively work on the file passed to myPackage.myProcedure(FileNameIN IN VARCHAR2) with the UTL_FILE package for example, you MUST create a DIRECTORY object (suppose you called it MYDIR), pointing to the same directory as the Java procedure then call UTL_FILE.FOPEN('MYDIR', FileNameIN, 'R') which will open it in read mode and return a UTL_FILE.FILE_TYPE handler for it.