Other Examples

You will find on the O'Reilly site three more interesting examples of using Java to extend the capabilities of PL/SQL or perform more complex datatype mapping.

utlzip.sql

Courtesy of reviewer Vadim Loevski, this Java class and corresponding package make zip/compression functionality available in PL/SQL. They also use the CREATE OR REPLACE JAVA statement to load a class directly into the database without relying on the loadjava command. Here is the header of the Java class creation statement:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "UTLZip" ASimport java.util.zip.*;import java.io.*;public class utlzip{ public static void compressfile(string infilename, string outfilename)...}/

And here is the "cover" for the Java method:

CREATE OR REPLACE PACKAGE utlzipIS PROCEDURE compressfile (p_in_file IN VARCHAR2, p_out_file IN VARCHAR2) AS LANGUAGE JAVA NAME 'UTLZip.compressFile(java.lang.String, java.lang.String)';END;

DeleteFile.java and deletefile.sql

Courtesy of reviewer Alex Romankeuich, this Java class and corresponding PL/SQL code demonstrate how to pass a collection (nested table or VARRAY) into an array in Java. The specific functionality implements the deletion of all files in the specified directory that have been modified since a certain date. To create the PL/SQL side of the equation, I first create a nested table of objects, and then pass that collection to Java through the use of the oracle.sql.ARRAY class:

CREATE TYPE file_details AS OBJECT ( dirname VARCHAR2 (30), deletedate DATE) CREATE TYPE file_table AS TABLE OF file_details; CREATE OR REPLACE PACKAGE delete_filesIS FUNCTION fdelete (tbl IN file_table) RETURN NUMBER AS LANGUAGE JAVA NAME 'DeleteFile.delete(oracle.sql.ARRAY) return int';END delete_files;

And here are the initial lines of the Java method. Note that Alex extracts the result set from the array structure and then iterates through that result set. See the DeleteFile.java script for the full implementation and extensive comments.

public class DeleteFile { public static int delete(oracle.sql.ARRAY tbl) throws SQLException { try { // Retrieve the contents of the table/varray as a result set ResultSet rs = tbl.getResultSet( ); for (int ndx = 0; ndx < tbl.length( ); ndx++) { rs.next( ); // Retrieve the array index and array element. int aryndx = (int)rs.getInt(1); STRUCT obj = (STRUCT)rs.getObject(2);

utlcmd.sql

Courtesy of reviewer Vadim Loevski, this Java class and corresponding package make it very easy to execute any operating system command from within PL/SQL.