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

From Eclipsepedia

Jump to: navigation, search
(Mixing IN and OUT arguments)
 
(15 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="#800000">procedure</font>''' bool_in_test<font color="#808030">(</font>x '''<font color="#800000">in</font>''' '''<font color="#800000">boolean</font>'''<font color="#808030">)</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="#000000">'''<font color="#7f0055">package</font>'''<font color="#7f0055"> test</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
package test;
   
+
 
  <font color="#3f7f59">// javase imports</font>
+
  // javase imports
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> java</font><font color="#7f0055">.</font><font color="#7f0055">util</font><font color="#7f0055">.</font><font color="#7f0055">Vector</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import java.util.List;
+
import java.util.ArrayList;
<font color="#3f7f59">// EclipseLink imports</font>
+
 
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">internal</font><font color="#7f0055">.</font><font color="#7f0055">helper</font><font color="#7f0055">.</font><font color="#7f0055">NonSynchronizedVector</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
// EclipseLink imports
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">logging</font><font color="#7f0055">.</font><font color="#7f0055">SessionLog</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import org.eclipse.persistence.logging.SessionLog;
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">platform</font><font color="#7f0055">.</font><font color="#7f0055">database</font><font color="#7f0055">.</font><font color="#7f0055">oracle</font><font color="#7f0055">.</font><font color="#7f0055">Oracle10Platform</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">platform</font><font color="#7f0055">.</font><font color="#7f0055">database</font><font color="#7f0055">.</font><font color="#7f0055">oracle</font><font color="#7f0055">.</font><font color="#7f0055">PLSQLStoredProcedureCall</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import org.eclipse.persistence.platform.database.oracle.Oracle10Platform;
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">queries</font><font color="#7f0055">.</font><font color="#7f0055">DataModifyQuery</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes;
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">sessions</font><font color="#7f0055">.</font><font color="#7f0055">DatabaseLogin</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall;
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">sessions</font><font color="#7f0055">.</font><font color="#7f0055">DatabaseSession</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import org.eclipse.persistence.queries.DataModifyQuery;
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">sessions</font><font color="#7f0055">.</font><font color="#7f0055">Project</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import org.eclipse.persistence.sessions.DatabaseLogin;
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">sessions</font><font color="#7f0055">.</font><font color="#7f0055">Session</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import org.eclipse.persistence.sessions.DatabaseSession;
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> static org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">platform</font><font color="#7f0055">.</font><font color="#7f0055">database</font><font color="#7f0055">.</font><font color="#7f0055">oracle</font><font color="#7f0055">.</font><font color="#7f0055">OraclePLSQLTypes</font><font color="#7f0055">.</font><font color="#7f0055">PLSQLBoolean</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
import org.eclipse.persistence.sessions.Project;
+
import org.eclipse.persistence.sessions.Session;
'''<font color="#7f0055">public</font>''' '''<font color="#7f0055">class</font>''' TestClass {
+
 
+
public class TestClass {
    '''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">String</font>''' DATABASE_USERNAME = <font color="#2a00ff">"scott"</font><nowiki>;
+
 
    </nowiki>'''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">String</font>''' DATABASE_PASSWORD = <font color="#2a00ff">"tiger"</font><nowiki>;
+
    public static String DATABASE_USERNAME = "scott";
    </nowiki>'''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">String</font>''' DATABASE_URL = <font color="#2a00ff">"jdbc:oracle:thin:@localhost:1521:ORCL"</font><nowiki>;
+
    public static String DATABASE_PASSWORD = "tiger";
    </nowiki>'''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">String</font>''' DATABASE_DRIVER = <font color="#2a00ff">"oracle.jdbc.driver.OracleDriver"</font><nowiki>;
+
    public static String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
   
+
    public static String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";
    </nowiki>'''<font color="#7f0055">public</font>''' '''<font color="#7f0055">static</font>''' '''<font color="#7f0055">void</font>''' main('''<font color="#7f0055">String</font>'''[] args) {
+
 
+
    public static void main(String[] args) {
        Project project = '''<font color="#7f0055">new</font>''' Project();
+
 
        DatabaseLogin login = '''<font color="#7f0055">new</font>''' DatabaseLogin();
+
        Project project = new Project();
        login.setUserName(DATABASE_USERNAME);
+
        DatabaseLogin login = new DatabaseLogin();
        login.setPassword(DATABASE_PASSWORD);
+
        login.setUserName(DATABASE_USERNAME);
        login.setConnectionString(DATABASE_URL);
+
        login.setPassword(DATABASE_PASSWORD);
        login.setDriverClassName(DATABASE_DRIVER);
+
        login.setConnectionString(DATABASE_URL);
        login.setDatasourcePlatform('''<font color="#7f0055">new</font>''' Oracle10Platform());
+
        login.setDriverClassName(DATABASE_DRIVER);
        project.setDatasourceLogin(login);
+
        login.setDatasourcePlatform(new Oracle10Platform());
        Session s = project.createDatabaseSession();
+
        project.setDatasourceLogin(login);
        s.setLogLevel(SessionLog.FINE);
+
        Session s = project.createDatabaseSession();
        ((DatabaseSession)s).login();
+
        s.setLogLevel(SessionLog.FINE);
+
        ((DatabaseSession)s).login();
        PLSQLStoredProcedureCall call = '''<font color="#7f0055">new</font>''' PLSQLStoredProcedureCall();
+
 
        call.setProcedureName(<font color="#2a00ff">"bool_in_test"</font>);
+
        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.addNamedArgument(<font color="#2a00ff">"X"</font>, PLSQLBoolean);
+
        call.setProcedureName("bool_in_test");
        DataModifyQuery query = '''<font color="#7f0055">new</font>''' DataModifyQuery();
+
        call.addNamedArgument("X", OraclePLSQLTypes.PLSQLBoolean);
        query.addArgument(<font color="#2a00ff">"X"</font>);
+
        DataModifyQuery query = new DataModifyQuery();
        query.setCall(call);
+
        query.addArgument("X");
        '''<font color="#7f0055">Vector</font>''' queryArgs = '''<font color="#7f0055">new</font>''' NonSynchronizedVector();
+
        query.setCall(call);
        queryArgs.add('''<font color="#7f0055">Integer</font>'''.valueOf(1));
+
        List queryArgs = new ArrayList();
        s.executeQuery(query, queryArgs);
+
        queryArgs.add(Integer.valueOf(1));
    }
+
        s.executeQuery(query, queryArgs);
}
+
    }
</font>
+
}
 +
</source>
  
 
The following snippet of the EclipseLink log shows the target procedure being invoked via an anonymous PL/SQL block:
 
The following snippet of the EclipseLink log shows the target procedure being invoked via an anonymous PL/SQL block:
  
<pre>
+
<source lang="text">
 
...
 
...
[EPS Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)--Thread(Thread[main,5,main])-- login successful
+
[EclipseLink 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])--
+
[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 75: 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 ===
 
=== 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):
 
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):
<font color="#000000">'''<font color="#800000">procedure</font>''' two_arg_test<font color="#808030">(</font>x '''<font color="#800000">in</font>''' '''<font color="#800000">boolean</font>'''<font color="#808030">,</font> y '''<font color="#800000">in</font>''' '''<font color="#800000">varchar</font>'''<font color="#808030">)</font>
+
<source lang="plsql">
</font>
+
procedure two_arg_test(x in boolean, y in varchar)
 +
</source>
  
 
The EclipseLink code:
 
The EclipseLink code:
<font color="#000000">
+
<source lang="java5">
...
+
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
'''<font color="#7f0055">import</font>'''<font color="#7f0055"> org</font><font color="#7f0055">.</font><font color="#7f0055">eclipse</font><font color="#7f0055">.</font><font color="#7f0055">persistence</font><font color="#7f0055">.</font><font color="#7f0055">platform</font><font color="#7f0055">.</font><font color="#7f0055">database</font><font color="#7f0055">.</font><font color="#7f0055">jdbc</font><font color="#7f0055">.</font><font color="#7f0055">JDBCTypes</font><font color="#7f0055"><nowiki>;</nowiki></font>
+
...
...
+
        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        PLSQLStoredProcedureCall call = '''<font color="#7f0055">new</font>''' PLSQLStoredProcedureCall();
+
        call.setProcedureName("two_arg_test");
        call.setProcedureName(<font color="#2a00ff">"two_arg_test"</font>);
+
        call.addNamedArgument("X", OraclePLSQLTypes.PLSQLBoolean);
        call.addNamedArgument(<font color="#2a00ff">"X"</font>, PLSQLBoolean);
+
        call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40);
        call.addNamedArgument(<font color="#2a00ff">"Y"</font>, JDBCTypes.VARCHAR_TYPE, 40);
+
        DataModifyQuery query = new DataModifyQuery();
        DataModifyQuery query = '''<font color="#7f0055">new</font>''' DataModifyQuery();
+
        query.addArgument("X");
        query.addArgument(<font color="#2a00ff">"X"</font>);
+
        query.addArgument("Y");
        query.addArgument(<font color="#2a00ff">"Y"</font>);
+
        query.setCall(call);
        query.setCall(call);
+
        List queryArgs = new ArrayList();
        '''<font color="#7f0055">Vector</font>''' queryArgs = '''<font color="#7f0055">new</font>''' NonSynchronizedVector();
+
        queryArgs.add(Integer.valueOf(0));
        queryArgs.add('''<font color="#7f0055">Integer</font>'''.valueOf(0));
+
        queryArgs.add("test");
        queryArgs.add(<font color="#2a00ff">"test"</font>);
+
        boolean worked = false;
        '''<font color="#7f0055">boolean</font>''' worked = '''<font color="#7f0055">false</font>'''<nowiki>;
+
        String msg = null;
        </nowiki>'''<font color="#7f0055">String</font>''' msg = '''<font color="#7f0055">null</font>'''<nowiki>;
+
        s.executeQuery(query, queryArgs);
        s.executeQuery(query, queryArgs);
+
</source>
</nowiki></font>
+
  
 
The EclipseLink log:
 
The EclipseLink log:
<pre>
+
<source lang="text">
[EPS Fine]: 2007.11.23 02:54:46.109--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
+
[EclipseLink Fine]: 2007.11.23 02:54:46.109--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 114: Line 120:
 
END;
 
END;
 
bind => [:1 => 0, :2 => test]
 
bind => [:1 => 0, :2 => test]
</pre>
+
</source>
  
 
=== Handling IN and OUT arguments ===
 
=== Handling IN and OUT arguments ===
 
The Stored Procedure may contain both <tt>IN</tt> and <tt>OUT</tt> arguments:
 
The Stored Procedure may contain both <tt>IN</tt> and <tt>OUT</tt> arguments:
 
+
<source lang="plsql">
<font color="#000000">'''<font color="#800000">procedure</font>''' two_arg_in_out<font color="#808030">(</font>x '''<font color="#800000">out</font>''' '''<font color="#800000">binary_integer</font>'''<font color="#808030">,</font> y '''<font color="#800000">in</font>''' '''<font color="#800000">varchar</font>'''<font color="#808030">)</font> '''<font color="#800000">as</font>'''
+
procedure two_arg_in_out(x out binary_integer, y in varchar) as
'''<font color="#800000">begin</font>'''
+
begin
  x :<font color="#808030"><nowiki>=</nowiki></font> <font color="#008c00">33</font><font color="#808030"><nowiki>;</nowiki></font>
+
x := 33;
'''<font color="#800000">end</font>'''<font color="#808030"><nowiki>;</nowiki></font>
+
end;
</font>
+
</source>
  
 
The EclipseLink code:
 
The EclipseLink code:
<font color="#000000">
+
<source lang="java5">
...
+
import static org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes.BinaryInteger;
        PLSQLStoredProcedureCall call = '''<font color="#7f0055">new</font>''' PLSQLStoredProcedureCall();
+
...
        call.setProcedureName(<font color="#2a00ff">"two_arg_in_out"</font>);
+
        PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
        call.addNamedOutputArgument(<font color="#2a00ff">"X"</font>, BinaryInteger);
+
        call.setProcedureName("two_arg_in_out");
        call.addNamedArgument(<font color="#2a00ff">"Y"</font>, JDBCTypes.VARCHAR_TYPE, 40);
+
        call.addNamedOutputArgument("X", OraclePLSQLTypes.BinaryInteger);
        DataReadQuery query = '''<font color="#7f0055">new</font>''' DataReadQuery();
+
        call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40);
        query.setCall(call);
+
        DataReadQuery query = new DataReadQuery();
        query.addArgument(<font color="#2a00ff">"Y"</font>);
+
        query.setCall(call);
        '''<font color="#7f0055">Vector</font>''' queryArgs = '''<font color="#7f0055">new</font>''' NonSynchronizedVector();
+
        query.addArgument("Y");
        queryArgs.add(<font color="#2a00ff">"testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds"</font>);
+
        List queryArgs = new ArrayList();
        '''<font color="#7f0055">boolean</font>''' worked = '''<font color="#7f0055">false</font>'''<nowiki>;
+
        queryArgs.add("testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds");
        </nowiki>'''<font color="#7f0055">String</font>''' msg = '''<font color="#7f0055">null</font>'''<nowiki>;
+
        boolean worked = false;
        </nowiki>'''<font color="#7f0055">Vector</font>''' results = ('''<font color="#7f0055">Vector</font>''')s.executeQuery(query, queryArgs);
+
        String msg = null;
        DatabaseRecord record = (DatabaseRecord)results.get(0);
+
        List results = (List)s.executeQuery(query, queryArgs);
        '''<font color="#7f0055">BigDecimal</font>''' x = ('''<font color="#7f0055">BigDecimal</font>''')record.get(<font color="#2a00ff">"X"</font>);
+
        DatabaseRecord record = (DatabaseRecord)results.get(0);
        '''<font color="#7f0055">if</font>''' (x.intValue() != 33) {
+
        BigDecimal x = (BigDecimal)record.get("X");
            '''<font color="#7f0055">System</font>'''.out.println(<font color="#2a00ff">"wrong x value"</font>);
+
        if (x.intValue() != 33) {
        }
+
            System.out.println("wrong x value");
</font>
+
        }
 
+
</source>
<pre>
+
<source lang="text">
[EPS Fine]: 2007.11.23 03:15:25.234--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
+
[EclipseLink Fine]: 2007.11.23 03:15:25.234--DatabaseSessionImpl(15674464)--Connection(5807702)--Thread(Thread[main,5,main])--
 
DECLARE
 
DECLARE
 
   Y_TARGET VARCHAR(40) := :1;
 
   Y_TARGET VARCHAR(40) := :1;
Line 157: Line 163:
 
END;
 
END;
 
bind => [:1 => testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds, X => :2]
 
bind => [:1 => testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds, X => :2]
</pre>
+
</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 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> block and after the target procedure has been invoked.
+
'''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.