Working with PL/SQL procedures in ADF BC Model

I believe this is very common use case when we need to invoke some PL/SQL procedure in our Java code in ADF BC model. The standard approach for that is to use JDBC callable statement. For example, we have a PL/SQL procedure:

create or replace procedure somePLSQLProcedure
(aStringParam Varchar2,
 aIntegerParam Number,
 aDateParam Date,
 aOutParam out Varchar2
 )

And Java method invoking this procedure using JDBC callable statement is going to look like this:

public String execPLSQLProcedure(String aStringParam, Integer aIntParam,
                                 Date aDateParam) {
    DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();
    CallableStatement statement =
      dbti.createCallableStatement(("BEGIN "+"somePLSQLProcedure(?,?,?,?);" +
                                    "END;"), 0);
    try {
        statement.setString(1, aStringParam);
        statement.setInt(2, aIntParam);
        statement.setDate(3, aDateParam);
        statement.registerOutParameter(4, Types.VARCHAR);
        statement.execute();
        return statement.getString(4);
    } catch (SQLException sqlerr) {
        throw new JboException(sqlerr);
    } finally {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException closeerr) {
            throw new JboException(closeerr);
        }
    }
}

Don't you think this piece of code is a little bit ugly? It is doing a prosaic thing - invoking an extremely simple PL/SQL procedure and it is so complicated. Moreover, there is no any control as for number and types of the procedure's parameters. So, writing this code each time whenever I need to invoke a PL/SQL procedure gets me a bit annoyed, and I developed some set of helper classes and interfaces in an effort to make it much easier. For example, a method invoking the same PL/SQL procedure should look like this:

public String execSmartPLSQLProcedure(String aStringParam,
                                      Integer aIntParam,
                                      Date aDateParam) {
    IStoredProc proc =
      StoredProc.getStoredProc(getDBTransaction(), "somePLSQLProcedure");
    proc.setStringValue("aStringParam", aStringParam);
    proc.setIntegerValue("aIntegerParam", aIntParam);
    proc.setDateValue("aDateParam", aDateParam);
    proc.execute();
    return proc.getStringValue("aOutParam");
}

Much better, isn't it?
Let's consider an example of invoking of a PL/SQL function. We have a PL/SQL function:

create or replace function somePLSQLFunction
(aStringParam Varchar2,
 aIntegerParam Number,
 aDateParam Date
 ) return Varchar2

And a Java method invoking this function is very simple:

public String execSmartPLSQLFunction (String aStringParam,
                                      Integer aIntParam,
                                      Date aDateParam) {
    IStoredProc proc =
      StoredProc.getStoredProc(getDBTransaction(), "somePLSQLFunction");
    proc.setStringValue("aStringParam", aStringParam);
    proc.setIntegerValue("aIntegerParam", aIntParam);
    proc.setDateValue("aDateParam", aDateParam);
    proc.execute();
    return proc.getStringValue("RESULT");
}

And what about working with complex SQL types? For example we have some SQL type representing an array of Varchar2:

CREATE OR REPLACE TYPE varchar2_array_type AS TABLE OF VARCHAR2(2000)

And our PL/SQL procedure accepts a parameter of this type:

create or replace procedure somePLSQLProcedure
(aStringParam Varchar2,
 aIntegerParam Number,
 aDateParam Date,
 aArrayParam varchar2_array_type,
 aOutParam out Varchar2
 )

So our Java code should look like this:

public String execSmartPLSQLProcedure(String aStringParam,
                                      Integer aIntParam,                                          
                                      oracle.jbo.domain.Date aDateParam,
                                      ArrayList<String> aArrayParam) {
    IStoredProc proc =
      StoredProc.getStoredProc(getDBTransaction(), "somePLSQLProcedure");
    proc.setStringValue("aStringParam", aStringParam);
    proc.setIntegerValue("aIntegerParam", aIntParam);
    proc.setDateValue("aDateParam", aDateParam);
    proc.setArrayValue("aArrayParam", aArrayParam, "VARCHAR2_ARRAY_TYPE");
    proc.execute();
    return proc.getStringValue("aOutParam");
}

One of the principal features of this approach is that you know all necessary information about the procedure's parameters right after the execution of StoredProc.getStoredProc(...) . So you have the full list of parameters containing their names and data types. In order to retrieve this information from the database  StoredProc class uses DatabaseMetaData method getProcedureColumns. This technique is described in details in this post.
The list of parameters allows us to control their names and types actually before execution of the procedure. Furthermore, we can assume that names of attributes of our Entity and names of parameters of PL/SQL procedures (inserting, updating and deleting the data) are equal and we can implement some method in an EntityImpl class performing DML operations:

private void executeDMLprocedure(String procedureName) {
    IStoredProc storedProc =
        StoredProc.getStoredProc(getDBTransaction(), procedureName);
    for (IStoredProcParam param : storedProc.getParams()) {
        if (param.isIn() || param.isInOut()) {
            param.setParamValue(getAttribute(param.getParamName()));
        }
    }
    storedProc.execute();
    for (IStoredProcParam param : storedProc.getParams()) {
        if (param.isOut())
            populateAttribute(getEntityDef().getAttributeIndexOf(
                              param.getParamName()),
                              param.getParamValue());
    }
}

The source code of this tool is available here. It can be deployed into ADF library and, perhaps, it can make your life a bit easier.

That's it!