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.
Difference between revisions of "EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures"
< EclipseLink | Examples | JPA
(→How to handle nonJDBC arguments for Oracle Stored Procedures) |
|||
| (33 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | + | == How to handle PLSQL arguments for Oracle Stored Procedures == | |
| − | == How to handle | + | |
The standard way of handling a Stored Procedure is to build an instance of <tt>org.eclipse.persistence.queries.StoredProcedureCall</tt>. However, the arguments must be compatible with the JDBC specification. | The standard way of handling a Stored Procedure is to build an instance of <tt>org.eclipse.persistence.queries.StoredProcedureCall</tt>. However, the arguments must be compatible with the JDBC specification. | ||
| − | To handle | + | To handle Oracle PLSQL arguments (e.g. BOOLEAN, PLS_INTEGER, PL/SQL record, etc.) a new sub-class has been created: <tt>org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall</tt>: |
For the target procedure: | For the target procedure: | ||
| − | + | ||
| + | <source lang="plsql"> | ||
| + | procedure bool_in_test(x in boolean) | ||
| + | </source> | ||
the EclipseLink code would be: | the EclipseLink code would be: | ||
| − | + | <source lang="java5"> | |
| − | + | 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); | ||
| + | } | ||
| + | } | ||
| + | </source> | ||
| + | |||
| + | The following snippet of the EclipseLink log shows the target procedure being invoked via an anonymous PL/SQL block: | ||
| + | |||
| + | <source lang="text"> | ||
| + | ... | ||
| + | [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 | DECLARE | ||
X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1); | X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1); | ||
| Line 98: | Line 77: | ||
END; | END; | ||
bind => [:1 => 1] | bind => [:1 => 1] | ||
| − | </ | + | </source> |
| + | |||
| + | '''NB''' - note the conversion of the Integer to a PL/SQL <tt>BOOLEAN</tt> type in the <tt>DECLARE</tt> stanza (as similar conversion is used for <tt>OUT BOOLEAN</tt> arguments). | ||
| + | |||
| + | '''NB2''' - the <tt>PLSQLStoredProcedureCall</tt> class is only supported for <tt>Oracle8Platform</tt>'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 <tt>PLSQLStoredProcedureCall</tt> 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): | ||
| + | <source lang="plsql"> | ||
| + | procedure two_arg_test(x in boolean, y in varchar) | ||
| + | </source> | ||
| + | |||
| + | The EclipseLink code: | ||
| + | <source lang="java5"> | ||
| + | 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); | ||
| + | </source> | ||
| + | |||
| + | The EclipseLink log: | ||
| + | <source lang="text"> | ||
| + | [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] | ||
| + | </source> | ||
| + | |||
| + | === Handling IN and OUT arguments === | ||
| + | The Stored Procedure may contain both <tt>IN</tt> and <tt>OUT</tt> arguments: | ||
| + | <source lang="plsql"> | ||
| + | procedure two_arg_in_out(x out binary_integer, y in varchar) as | ||
| + | begin | ||
| + | x := 33; | ||
| + | end; | ||
| + | </source> | ||
| + | |||
| + | The EclipseLink code: | ||
| + | <source lang="java5"> | ||
| + | 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"); | ||
| + | } | ||
| + | </source> | ||
| + | <source lang="text"> | ||
| + | [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] | ||
| + | </source> | ||
| + | |||
| + | '''NB''' - note that the order in which arguments are bound at runtime must be altered. Anonymous PL/SQL blocks must process the ordinal markers (<tt>:1</tt>, <tt>:2</tt>) for all the <tt>IN</tt> arguments first, then the <tt>OUT</tt> 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 <tt>DECLARE</tt> stanza and after the target procedure has been invoked. | ||
| + | |||
| + | === Handling IN OUT arguments === | ||
| + | Anonymous PL/SQL blocks '''cannot''' natively handle <tt>INOUT</tt> arguments. EclipseLink must split the argument into two parts: an <tt>IN-</tt>half and an <tt>OUT-</tt>half: | ||
| + | <source lang="plsql"> | ||
| + | procedure two_args_inout(x varchar, y in out boolean) as | ||
| + | begin | ||
| + | y := false; | ||
| + | end; | ||
| + | </source> | ||
| + | <source lang="java5"> | ||
| + | ... | ||
| + | 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"); | ||
| + | } | ||
| + | </source> | ||
| + | <source lang="text"> | ||
| + | [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] | ||
| + | </source> | ||
| + | |||
| + | '''NB''' - note how <tt>Y</tt> is split in two, using <tt>:2</tt> and <tt>:3</tt> ordinal markers. | ||
Latest revision as of 12:47, 2 December 2010
Contents
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.