Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures

< EclipseLink‎ | Examples‎ | JPA
Revision as of 16:21, 23 November 2007 by Michael.norman.oracle.com (Talk | contribs) (Mixing IN and OUT arguments)

{available as of EclipseLink 1.0M2}

How to handle nonJDBC 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 nonJDBC 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.Vector;

// EclipseLink imports
import org.eclipse.persistence.internal.helper.NonSynchronizedVector;
import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.platform.database.oracle.Oracle10Platform;
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;
import static org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes.PLSQLBoolean;

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", PLSQLBoolean);
        DataModifyQuery query = new DataModifyQuery();
        query.addArgument("X");
        query.setCall(call);
        Vector queryArgs = new NonSynchronizedVector();
        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:

...
[EPS Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])-- login successful
[EPS 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]

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", PLSQLBoolean);
        call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40);
        DataModifyQuery query = new DataModifyQuery();
        query.addArgument("X");
        query.addArgument("Y");
        query.setCall(call);
        Vector queryArgs = new NonSynchronizedVector();
        queryArgs.add(Integer.valueOf(0));
        queryArgs.add("test");
        boolean worked = false;
         String msg = null;
         s.executeQuery(query, queryArgs);
 

The EclipseLink log:

[EPS 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]

Mixing 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:


...
        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.setProcedureName("two_arg_in_out");
        call.addNamedOutputArgument("X", BinaryInteger);
        call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40);
        DataReadQuery query = new DataReadQuery();
        query.setCall(call);
        query.addArgument("Y");
        Vector queryArgs = new NonSynchronizedVector();
        queryArgs.add("testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds");
        boolean worked = false;
         String msg = null;
         Vector results = (Vector)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");
        }

[EPS 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]

Back to the top