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

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

(How to handle nonJDBC arguments for Oracle Stored Procedures)
(Mixing regular JDBC arguments with nonJDBC arguments)
Line 79: Line 79:
  
 
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>
  <font color="#000000">'''<font color="#575757">procedure</font>''' two_args_test<font color="#555555">(</font>x '''<font color="#575757">IN</font>''' '''<font color="#575757">BOOLEAN</font>'''<font color="#555555">)</font></font>
+
</font>
 
+
  
 
<pre>
 
<pre>

Revision as of 15:52, 23 November 2007

{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)

[EPS Fine]: 2007.11.23 02:47:38.562--DatabaseSessionImpl(15055830)--Connection(23438274)--Thread(Thread[main,5,main])--
DECLARE
  X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1);
  Y_TARGET VARCHAR(255) := :2;
BEGIN
  two_args_test(X=>X_TARGET, Y=>Y_TARGET);
END;

Back to the top