Difference between revisions of "EclipseLink/Examples/JPA/nonJDBCArgsToStoredProcedures"

From Eclipsepedia

Jump to: navigation, search
(How to handle nonJDBC arguments for Oracle Stored Procedures)
 
(33 intermediate revisions by 3 users not shown)
Line 1: Line 1:
''{available as of EclipseLink 1.0M2}''
+
== How to handle PLSQL arguments for Oracle Stored Procedures ==
== How to handle nonJDBC arguments for Oracle Stored Procedures ==
+
  
 
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 nonJDBC 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>:
+
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:
<font color="#000000">'''<font color="#575757">procedure</font>''' bool_test<font color="#555555">(</font>x '''<font color="#575757">IN</font>''' '''<font color="#575757">BOOLEAN</font>'''<font color="#555555">)</font></font>
+
 
 +
<source lang="plsql">
 +
procedure bool_in_test(x in boolean)
 +
</source>
  
 
the EclipseLink code would be:
 
the EclipseLink code would be:
+
<source lang="java5">
<font color="RED">'''package'''</font> test<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
package test;
+
  <font color="GREEN">''// javase imports
+
''</font><font color="RED">'''import'''</font> java<font size="+1" color="BLUE">'''.'''</font>util<font size="+1" color="BLUE">'''.'''</font>Vector<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
<font color="GREEN">''// EclipseLink imports
+
''</font><font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>internal<font size="+1" color="BLUE">'''.'''</font>helper<font size="+1" color="BLUE">'''.'''</font>NonSynchronizedVector<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>logging<font size="+1" color="BLUE">'''.'''</font>SessionLog<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>platform<font size="+1" color="BLUE">'''.'''</font>database<font size="+1" color="BLUE">'''.'''</font>oracle<font size="+1" color="BLUE">'''.'''</font>Oracle10Platform<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>platform<font size="+1" color="BLUE">'''.'''</font>database<font size="+1" color="BLUE">'''.'''</font>oracle<font size="+1" color="BLUE">'''.'''</font>PLSQLStoredProcedureCall<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>queries<font size="+1" color="BLUE">'''.'''</font>DataModifyQuery<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>sessions<font size="+1" color="BLUE">'''.'''</font>DatabaseLogin<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>sessions<font size="+1" color="BLUE">'''.'''</font>DatabaseSession<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>sessions<font size="+1" color="BLUE">'''.'''</font>Project<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
<font color="RED">'''import'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>sessions<font size="+1" color="BLUE">'''.'''</font>Session<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
<font color="RED">'''import'''</font> <font color="RED">'''static'''</font> org<font size="+1" color="BLUE">'''.'''</font>eclipse<font size="+1" color="BLUE">'''.'''</font>persistence<font size="+1" color="BLUE">'''.'''</font>platform<font size="+1" color="BLUE">'''.'''</font>database<font size="+1" color="BLUE">'''.'''</font>oracle<font size="+1" color="BLUE">'''.'''</font>OraclePLSQLTypes<font size="+1" color="BLUE">'''.'''</font>PLSQLBoolean<font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
<font color="RED">'''public'''</font> <font color="RED">'''class'''</font> TestClass <font size="+1" color="BLUE">'''{'''</font>
+
+
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> String DATABASE_USERNAME <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="PURPLE">"scott"</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> String DATABASE_PASSWORD <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="PURPLE">"tiger"</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> String DATABASE_URL <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="PURPLE">"jdbc:oracle:thin:@localhost:1521:ORCL"</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> String DATABASE_DRIVER <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="PURPLE">"oracle.jdbc.driver.OracleDriver"</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
   
+
    <font color="RED">'''public'''</font> <font color="RED">'''static'''</font> <font color="RED">'''void'''</font> main<font size="+1" color="BLUE">'''('''</font>String<font size="+1" color="BLUE">'''['''</font><font size="+1" color="BLUE">''']'''</font> args<font size="+1" color="BLUE">''')'''</font> <font size="+1" color="BLUE">'''{'''</font>
+
+
        Project project <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> Project<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
        DatabaseLogin login <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> DatabaseLogin<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        login<font size="+1" color="BLUE">'''.'''</font>setUserName<font size="+1" color="BLUE">'''('''</font>DATABASE_USERNAME<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
        login<font size="+1" color="BLUE">'''.'''</font>setPassword<font size="+1" color="BLUE">'''('''</font>DATABASE_PASSWORD<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        login<font size="+1" color="BLUE">'''.'''</font>setConnectionString<font size="+1" color="BLUE">'''('''</font>DATABASE_URL<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
        login<font size="+1" color="BLUE">'''.'''</font>setDriverClassName<font size="+1" color="BLUE">'''('''</font>DATABASE_DRIVER<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        login<font size="+1" color="BLUE">'''.'''</font>setDatasourcePlatform<font size="+1" color="BLUE">'''('''</font><font color="RED">'''new'''</font> Oracle10Platform<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
        project<font size="+1" color="BLUE">'''.'''</font>setDatasourceLogin<font size="+1" color="BLUE">'''('''</font>login<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        Session s <font size="+1" color="BLUE"><nowiki>=</nowiki></font> project<font size="+1" color="BLUE">'''.'''</font>createDatabaseSession<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
        s<font size="+1" color="BLUE">'''.'''</font>setLogLevel<font size="+1" color="BLUE">'''('''</font>SessionLog<font size="+1" color="BLUE">'''.'''</font>FINE<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        <font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">'''('''</font>DatabaseSession<font size="+1" color="BLUE">''')'''</font>s<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''.'''</font>login<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        PLSQLStoredProcedureCall call <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> PLSQLStoredProcedureCall<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        call<font size="+1" color="BLUE">'''.'''</font>setProcedureName<font size="+1" color="BLUE">'''('''</font><font color="PURPLE">"bool_in_test"</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
        call<font size="+1" color="BLUE">'''.'''</font>addNamedArgument<font size="+1" color="BLUE">'''('''</font><font color="PURPLE">"X"</font><font size="+1" color="BLUE">''','''</font> PLSQLBoolean<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        DataModifyQuery query <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> DataModifyQuery<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
        query<font size="+1" color="BLUE">'''.'''</font>addArgument<font size="+1" color="BLUE">'''('''</font><font color="PURPLE">"X"</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        query<font size="+1" color="BLUE">'''.'''</font>setCall<font size="+1" color="BLUE">'''('''</font>call<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
        Vector queryArgs <font size="+1" color="BLUE"><nowiki>=</nowiki></font> <font color="RED">'''new'''</font> NonSynchronizedVector<font size="+1" color="BLUE">'''('''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
        queryArgs<font size="+1" color="BLUE">'''.'''</font>add<font size="+1" color="BLUE">'''('''</font>Integer<font size="+1" color="BLUE">'''.'''</font>valueOf<font size="+1" color="BLUE">'''('''</font><font color="BROWN">1</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
        s<font size="+1" color="BLUE">'''.'''</font>executeQuery<font size="+1" color="BLUE">'''('''</font>query<font size="+1" color="BLUE">''','''</font> queryArgs<font size="+1" color="BLUE">''')'''</font><font size="+1" color="BLUE">'''<nowiki>;</nowiki>'''</font>
+
+
    <font size="+1" color="BLUE">'''}'''</font>
+
<font size="+1" color="BLUE">'''}'''</font>
+
  
<pre>
+
// javase imports
[EPS Info]: 2007.11.23 01:03:23.515--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])--TopLink, version: Eclipse Persistence Services - @VERSION@ (Build @BUILD_NUMBER@)
+
import java.util.List;
[EPS Config]: 2007.11.23 01:03:23.531--DatabaseSessionImpl(15674464)--Connection(4877503)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
+
import java.util.ArrayList;
platform=>Oracle10Platform
+
 
user name=> "scott"
+
// EclipseLink imports
datasource URL=> "jdbc:oracle:thin:@localhost:1521:ORCL"
+
import org.eclipse.persistence.logging.SessionLog;
))
+
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
[EPS Config]: 2007.11.23 01:03:23.875--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@localhost:1521:ORCL
+
import org.eclipse.persistence.platform.database.oracle.Oracle10Platform;
User: SCOTT
+
import org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes;
Database: Oracle  Version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
+
import org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall;
With the Partitioning, OLAP and Data Mining options
+
import org.eclipse.persistence.queries.DataModifyQuery;
Driver: Oracle JDBC driver  Version: 10.2.0.1.0
+
import org.eclipse.persistence.sessions.DatabaseLogin;
[EPS Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])-- login successful
+
import org.eclipse.persistence.sessions.DatabaseSession;
[EPS Fine]: 2007.11.23 01:03:23.968--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
+
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]
</pre>
+
</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

[edit] 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.

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

[edit] 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.

[edit] 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.