Oracle JPublisher to call a stored procedure that use PL/SQL Object Collections

When using java, one might need to call up either a pl/sql procedure, functions or a package stored on the database. One way to do this is to use the standard JDBC (Java Database Connectivity) wrapper, but the Pl/SQL data complexity could make that operation difficult, as might be the case with a Pl/SQL object collection.

JPublisher is an Oracle utility that helps create a java wrapper that will represent database entities: User-defined SQL object types, Object reference types (REF types), User-defined SQL collection types (VARRAY types or nested table types), PL/SQL packages, SQL queries and DML statements. You can find more information about JPublisher here: link. 

This utility generates the accessory methods, getXXX() and setXXX(), corresponding to each attribute of an object type. If the object type has stored procedures, then JPublisher can generate wrapper methods to invoke these stored procedures. In this scenario, the wrapper method is a Java method that invokes a stored procedure which runs in Oracle Database.

There is a Pl/SQL object collection as follows:

CREATE OR REPLACE TYPE MYSCHEMA.MY_OBJECT AS OBJECT (
  STR_DATA VARCHAR2(4),
  NMB_DATA NUMBER,
  DATE_DATA DATE
);

CREATE OR REPLACE TYPE MYSCHEMA.MY_ARRAY AS TABLE OF MYSCHEMA.MY_OBJECT;

The Collection is the store procedure input and output as follows:

CREATE OR REPLACE PROCEDURE MYSCHEMA.MY_PROCEDURE
(p_input IN MY_ARRAY, p_output OUT MY_ARRAY) IS
EndCounter NUMBER := 0;
BEGIN
p_output := MY_ARRAY();
  IF p_input.LAST > 0 THEN
    EndCounter := p_input.LAST;

    FOR i in 1..EndCounter LOOP
      p_output.EXTEND(1);
      p_output(i):=p_input(i);
    END LOOP;

  END IF;

END;

Creating a JPublisher wrapper from a command prompt is very simple. Be sure to have set the ORACLE_HOME, environment variable to the oracle client path, and the classpath as in this next code snippet:

set ORACLE_HOME=<Path-to-oracle-client>

set CLASSPATH=.;%ORACLE_HOME%\jdbc\lib\ojdbc14.jar;
%ORACLE_HOME%\jdbc\lib\orai18n.jar;
%ORACLE_HOME%\sqlj\lib\translator.jar;
%ORACLE_HOME%\sqlj\lib\runtime12.jar;
%ORACLE_HOME%\sqlj\runtime12ee.jar;
<Path-to-oracle-driver>\ojdbc5.jar

Once the environment is setting the command syntax to generate wrapper classes is the follow:
jpub -user=<DB-USER>/<DB-PWD> -url=jdbc:oracle:thin:@<DB-IP-Address>:<DB-Port>:<DB-Service-Name> -codegen=iso -dir=<Path-Where-Create-source-code> -package=<package-for-generated-classes> -sql=<Sql-object-name> -tostring=true

An example of a possible command for generating the necessary classes could be the following:
jpub -user=MYSCHEMA/MYSCHEMA -url=jdbc:oracle:thin:@localhost:1521:XE -codegen=iso -dir=/JPublisher/jpubgen/source -package=org.annotations.wrap -sql=MY_OBJECT:MyObject,MY_ARRAY:MyArray -tostring=true

The java classes created in this case are: MyObject, MyObjectRef, MySchema and can be used in a java application that call MYSCHEMA.MY_PROCEDURE as in the follow snipes.

//Start initialize input array
MyObject[] myInputObject = new MyObject[4];
myInputObject[0] = new MyObject("AAAA",new BigDecimal(0),new Timestamp(System.currentTimeMillis()));
myInputObject[1] = new MyObject("BBBB",new BigDecimal(1),new Timestamp(System.currentTimeMillis()));
myInputObject[2] = new MyObject("CCCC",new BigDecimal(2),new Timestamp(System.currentTimeMillis()));
myInputObject[3] = new MyObject("DDDD",new BigDecimal(3),new Timestamp(System.currentTimeMillis()));
MyArray arrayInput = new MyArray (myInputObject);
//End initialize input array

//CallableStatement
Connection connection = getSimpleConnection();
CallableStatement stmt = connection.prepareCall("BEGIN MYSCHEMA.MY_PROCEDURE(?,?); END;");

//Rec output parameters. 
stmt.registerOutParameter(2, MyArray._SQL_TYPECODE, MyArray._SQL_NAME);

//set input array parameter on the Callable Statement 
stmt.setObject(1, arrayInput);

//execute procedure
stmt.execute();
 
//get output paramter 
MyArray arrayOutput = new MyArray(); 
arrayOutput = (MyArray) arrayOutput.create((Datum) stmt.getObject(2), 0);
    
//Close CallableStatement
stmt.close();

//Gets out the Output Array printing array content 
for (MyObject current : arrayOutput.getArray()) 
  log(current.toString());