Advanced PL/SQL integration with ADF (new API)

Source: GitHub

Till ADF 11g, we have been integrating ADF with PL-SQL modules having user-defined types with "oracle.sql" packages. We have used oracle.sql.StructDescriptor and oracle.sql.ArrayDescriptor to create implementations of user-defined types on Oracle database.

Since the migration to 12c, we have been getting a warning that most of oracle.sql's APIs have been deprecated.

Deprecated code

The new solution is to use java.sql.Struct (not STRUCT) and java.sql.Array (not ARRAY).

However, using the Array API has been made a bit tricky. Now we do not have the support of ArrayDescriptor to create a definition object for the user-defined type. Let us see how exactly we go about it.

This is the use case we will solve:
1) Call a packaged procedure with a user-defined type as IN parameter.
2) Modify the object and return as an OUT parameter of a user-defined type.
3) Intercept the out parameter in ADF and read the modified contents.

We will be using the default HR schema. Here are the objects being used:

User-defined type and array

To create an implementation of the user-defined type, we create an Employee object, which implements java.sql.SQLData. This class will be shared across Model and ViewController projects as a shared type.

Note - please refer to my old post - Complex datatypes for ADF AM/VO client service methods - for more details on creating a custom object for AM or VO service methods.

The SQLData interface has three implementation methods - getSQLTypeName, readSQL and writeSQL. An implementation of this interface requires that the user-defined object name is set in this object (EMP_TYPE in this case). The implementing class should have member variables which should mimic the user-defined object.

Employee object implementing SQLData interface

The writeSQL method is responsible for mapping the Java object to the IN parameter type. This method needs to be implemented when you need to send an array from ADF to PL/SQL IN parameter.
The readSQL method is responsible for mapping the OUT parameter to the Java object. This method needs to be implemented when your PL/SQL API sends an array as OUT parameter. - Implementation of SQLData interface

We now have the Employee object ready to mimic the EMP_TYPE_T user-defined object. We would be creating an array of this object.

But, there's one last step! The java.sql.connection object should be unwrapped to expose the oracle.jdbc.OracleConnection interface. This interface has a method createOracleArray, which takes the object name (String) and the actual array.

AMImpl method

Interestingly, the internal representation of this Array would be to create an array of Struct objects, although we do not explicitly do so. This can be observed when we de-construct the output array:

De-constructing OUT parameter

So let's now see how it goes. We send an array of Employee objects. This array is modified in the procedure. A new array is sent as an out parameter, which is de-constructed into Employee objects.