Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures
{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]