EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures

How to handle PLSQL arguments for Oracle Stored Procedures

The standard way of handling a Stored Procedure is to build an instance of org.eclipse.persistence.queries.StoredProcedureCall. However, the arguments must be compatible with the JDBC specification.

To handle Oracle PLSQL arguments (e.g. BOOLEAN, PLS_INTEGER, PL/SQL record, etc.) a new sub-class has been created: org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall:

For the target procedure:

PROCEDURE bool_in_test(x IN BOOLEAN)

the EclipseLink code would be:

package test;
 
 // javase imports
import java.util.List;
import java.util.ArrayList;
 
// EclipseLink imports
import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
import org.eclipse.persistence.platform.database.oracle.Oracle10Platform;
import org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes;
import org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall;
import org.eclipse.persistence.queries.DataModifyQuery;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.sessions.Project;
import org.eclipse.persistence.sessions.Session;
 
public class TestClass {
 
    public static String DATABASE_USERNAME = "scott";
    public static String DATABASE_PASSWORD = "tiger";
    public static String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
    public static String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";
 
    public static void main(String[] args) {
 
        Project project = new Project();
        DatabaseLogin login = new DatabaseLogin();
        login.setUserName(DATABASE_USERNAME);
        login.setPassword(DATABASE_PASSWORD);
        login.setConnectionString(DATABASE_URL);
        login.setDriverClassName(DATABASE_DRIVER);
        login.setDatasourcePlatform(new Oracle10Platform());
        project.setDatasourceLogin(login);
        Session s = project.createDatabaseSession();
        s.setLogLevel(SessionLog.FINE);
        ((DatabaseSession)s).login();
 
        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.setProcedureName("bool_in_test");
        call.addNamedArgument("X", OraclePLSQLTypes.PLSQLBoolean);
        DataModifyQuery query = new DataModifyQuery();
        query.addArgument("X");
        query.setCall(call);
        List queryArgs = new ArrayList();
        queryArgs.add(Integer.valueOf(1));
        s.executeQuery(query, queryArgs);
    }
}

The following snippet of the EclipseLink log shows the target procedure being invoked via an anonymous PL/SQL block:

...
[EclipseLink Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])-- login successful
[EclipseLink Fine]: 2007.11.23 01:03:23.968--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
DECLARE
  X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
BEGIN
  bool_in_test(X=>X_TARGET);
END;
	bind => [:1 => 1]

NB - note the conversion of the Integer to a PL/SQL BOOLEAN type in the DECLARE stanza (as similar conversion is used for OUT BOOLEAN arguments).

NB2 - the PLSQLStoredProcedureCall class is only supported for Oracle8Platform's or higher.

Mixing regular JDBC arguments with nonJDBC arguments

A Stored Procedure may have a mix of regular and nonJDBC arguments. EclipseLink supports scenarios where all the arguments are regular JDBC types; however, when at least one argument is a nonJDBC type, then the PLSQLStoredProcedureCall class must be used. Additionally, because the target procedure is being invoked via an anonymous PL/SQL block, some additional information may be required for the JDBC type (length, scale or precision):

PROCEDURE two_arg_test(x IN BOOLEAN, y IN VARCHAR)

The EclipseLink code:

import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
...
        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.setProcedureName("two_arg_test");
        call.addNamedArgument("X", OraclePLSQLTypes.PLSQLBoolean);
        call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40);
        DataModifyQuery query = new DataModifyQuery();
        query.addArgument("X");
        query.addArgument("Y");
        query.setCall(call);
        List queryArgs = new ArrayList();
        queryArgs.add(Integer.valueOf(0));
        queryArgs.add("test");
        boolean worked = false;
        String msg = null;
        s.executeQuery(query, queryArgs);

The EclipseLink log:

[EclipseLink Fine]: 2007.11.23 02:54:46.109--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
DECLARE
  X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
  Y_TARGET VARCHAR(40) := :2;
BEGIN
  two_arg_test(X=>X_TARGET, Y=>Y_TARGET);
END;
	bind => [:1 => 0, :2 => test]

Handling IN and OUT arguments

The Stored Procedure may contain both IN and OUT arguments:

PROCEDURE two_arg_in_out(x OUT BINARY_INTEGER, y IN VARCHAR) AS
BEGIN
 x := 33;
END;

The EclipseLink code:

import static org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes.BinaryInteger;
...
        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.setProcedureName("two_arg_in_out");
        call.addNamedOutputArgument("X", OraclePLSQLTypes.BinaryInteger);
        call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40);
        DataReadQuery query = new DataReadQuery();
        query.setCall(call);
        query.addArgument("Y");
        List queryArgs = new ArrayList();
        queryArgs.add("testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds");
        boolean worked = false;
        String msg = null;
        List results = (List)s.executeQuery(query, queryArgs);
        DatabaseRecord record = (DatabaseRecord)results.get(0);
        BigDecimal x = (BigDecimal)record.get("X");
        if (x.intValue() != 33) {
            System.out.println("wrong x value");
        }

[EclipseLink Fine]: 2007.11.23 03:15:25.234--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
DECLARE
  Y_TARGET VARCHAR(40) := :1;
  X_TARGET BINARY_INTEGER;
BEGIN
  two_arg_in_out(X=>X_TARGET, Y=>Y_TARGET);
  :2 := X_TARGET;
END;
	bind => [:1 => testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds, X => :2]

NB - note that the order in which arguments are bound at runtime must be altered. Anonymous PL/SQL blocks must process the ordinal markers (:1, :2) for all the IN arguments first, then the OUT args. Inside the block, the arguments are passed in the correct order for the target procedure, but EclipseLink must manage the bind order in the DECLARE stanza and after the target procedure has been invoked.

Handling IN OUT arguments

Anonymous PL/SQL blocks cannot natively handle INOUT arguments. EclipseLink must split the argument into two parts: an IN-half and an OUT-half:

PROCEDURE two_args_inout(x VARCHAR, y IN OUT BOOLEAN) AS
BEGIN
  y := FALSE;
 END;

...
        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.setProcedureName("two_args_inout");
        call.addNamedArgument("X", JDBCTypes.VARCHAR_TYPE, 20);
        call.addNamedInOutputArgument("Y", OraclePLSQLTypes.PLSQLBoolean);
        DataReadQuery query = new DataReadQuery();
        query.addArgument("X");
        query.addArgument("Y");
        query.setCall(call);
        List queryArgs = new ArrayList();
        queryArgs.add("test");
        queryArgs.add(Integer.valueOf(1));
        List results = (List)s.executeQuery(query, queryArgs);
        DatabaseRecord record = (DatabaseRecord)results.get(0);
        Integer bool2int = (Integer)record.get("Y");
        if (bool2int.intValue() != 0) {
            System.out.println("wrong bool2int value");
        }

[EclipseLink Fine]: 2007.11.23 03:39:55.000--DatabaseSessionImpl(25921812)--Connection(33078541)--Thread(Thread[main,5,main])--
DECLARE
  X_TARGET VARCHAR(20) := :1;
  Y_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:2);
BEGIN
  two_args_inout(X=>X_TARGET, Y=>Y_TARGET);
  :3 := SYS.SQLJUTL.BOOL2INT(Y_TARGET);
END;
	bind => [:1 => test, :2 => 1, Y => :3]

NB - note how Y is split in two, using :2 and :3 ordinal markers.