0

I am using WLS 12.1.3 and oracle is 10.2.0, I am using weblogic datasource to get connection This is the code,

   CallableStatement cst = null;
    try {
        cst = conn
                .prepareCall("{call myProc(?,?,?,?,?,?,?,?)}");
        final String typeTableName = "studentdetails";

        cst.setInt(1, student.getEmpid());
        cst.setInt(2, student.getOrgid());
        cst.setInt(3, student.getYearid());
        cst.setString(4,  student.getClassType());
        cst.setInt(5, student.getStudentid());
        cst.registerOutParameter(6, Types.ARRAY, typeTableName);
        cst.registerOutParameter(7, java.sql.Types.VARCHAR);
        cst.registerOutParameter(8, java.sql.Types.VARCHAR);
                        long startTime=System.currentTimeMillis();
        cst.execute();
                        String dat=cst.getString(7);
                         //Array arr = cst.getArray(6);
                        long endTime=System.currentTimeMillis();

        if (null != cst.getObject(6)) {
            data = (Object[]) ((Array) cst.getObject(6)).getArray();
        }

This is the connection object and callable statement obj 

cst = (weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCallableStatementWrapper) weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCallableStatementWrapper@53

conn = (weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection) [weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection-XMLJDBC_Data_Source-1-3, oracle.jdbc.driver.LogicalConnection@34505466]

If I use datasource, I am getting cst.getObject(6) as null, but if use normal jdbc connection I am getting the object. Please suggest how to resolve this issue

Getting this exception "ERROR:get_item_uda_data:ORA-02089: COMMIT is not allowed in a subordinate session exception from the procedure"

user3428736
  • 864
  • 2
  • 13
  • 33
  • Are the datasource and driver using the same driver (and version)? Is one thin and the other OCI, maybe? (Just guessing...) – Alex Poole Jun 16 '17 at 08:40
  • I am using this Driver Class Name: oracle.jdbc.xa.client.OracleXADataSource in Weblogic server and trying to get connection from datasource. All DML/DDL queries and procedure are working fine but not with this cst.registerOutParameter(6, Types.ARRAY, typeTableName);collection as out parameter – user3428736 Jun 16 '17 at 12:08
  • I am getting ERROR:get_item_uda_data:ORA-02089: COMMIT is not allowed in a subordinate session exception from the procedure – user3428736 Jun 19 '17 at 09:14

1 Answers1

0

Your issue is very likely related to the procedure "myProc" trying to call "commit" within an active JTA transaction having auto-commit enabled.

In the JDBC context there will be no issue in this case, since it probably doesn't auto-commit in your environment.

If my assumption is correct, you just need to remove the "commit" inside your "myProc". The other option is to call the procedure outside of a JTA transaction (just annotate your bean with "TransactionAttribute.NOT_SUPPORTED").

Peter Branforn
  • 1,679
  • 3
  • 17
  • 29
  • I have added conn.setAutoCommit(false) to make transaction commit as false – user3428736 Jun 20 '17 at 09:35
  • I just searched for the problem outside of this scope and I think it will not be enough to just disable the auto-commit. Checkout this other question/answer I just found in _the_stack_: https://stackoverflow.com/questions/29117201/error-with-jpa-transaction-when-calling-a-stored-procedure – Peter Branforn Jun 20 '17 at 10:44