Oracle: How to Invoke a Java class in PL/SQL

The advantage of using Java class for accessing systems files is for richer set of file IO Capabilities.
Oracle's UTL_FILE package provides a limited functionality for accessing system files.
However, Java has a far richer set of File IO capabilities, allowing developers to remove files, add directories, and so on.

The following example illustrates some simple steps to invoke a Java class in Oracle PL/SQL.

Objective of this example is to get list of all files under a specified directory and insert the data into an Oracle Table.

Step 1#. Create a Java Class in Oracle which could access system files.--Following Java class is used to read through a directory for Files
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "javaReadFolder"AS import java.io.*;
import java.sql.*;

  public static void getList(String pdirectory)
  throws SQLException{
--pdirectory : Full physical path of the directory
File path = new File( pdirectory );
String[] list = path.list();
String element;
int jobrunid;
jobrunid = 0;
--EXECUTE a SQL statement: Get runid
#sql { SELECT seq_job_runid.NEXTVAL into :jobrunid from dual};
--Loops through all the files from the specified directory and insert the file rows in the table.

for(int i = 0; i < list.length; i++)
{
  element = list[i];
  --EXECUTE a SQL statement
  #sql { INSERT INTO tb_load_files (runid, filename,processed)
  VALUES (:jobrunid, :element,'Y') };
}

}
}
/
Using a Java stored procedure it is possible to manipulate operating system files from PL/SQL: Get the Java File Handling class :
http://www.oracle-base.com/articles/8i/FileHandlingFromPLSQL.php

Step 2#. Create Oracle PL/SQL Procedure wrapper which could invoke the Java Class
PROCEDURE GET_DIR_LIST( p_directory in VARCHAR2) AS LANGUAGE JAVA
NAME 'javaReadFolder.getList(java.lang.String)' ;

Step 3#. Execute the Oracle Proceduer
EXEC GET_DIR_LIST('/home/xml/export')public class javaReadFolder
{