3 Replies Latest reply: Sep 25, 2008 4:51 AM by Bahar RSS

    TP4 - View Object with REF CURSOR - SQLException

    Bahar
      Hi,

      I want to run the example from How to Create a View Object on a REF CURSOR Advanced View Object Techniques in the Developers Guide.

      AdvancedViewObjectExamples.zip from [http://www.oracle.com/technology/documentation/jdev/b25947_01/index.html]
      The workspace is from 10.1.3 and has been migrated in 11g TP4. Only change is import oracle.jdbc.OracleTypes; instead of import oracle.jdbc.driver.OracleTypes; in RequestForTechImpl.java

      When I run the application module and query the view object RequestForTech, the following exception has been thrown:

      Exception in thread "main" oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.sql.SQLException, msg=Closed Statement: next
      at devguide.advanced.refcursor.RequestForTechImpl.hasNextForCollection(RequestForTechImpl.java:123)
      at devguide.advanced.refcursor.RequestForTechImpl.storeNewResultSet(RequestForTechImpl.java:207)
      at devguide.advanced.refcursor.RequestForTechImpl.executeQueryForCollection(RequestForTechImpl.java:51)
      at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:1010)
      at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:1160)
      at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:1080)
      at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:1074)
      at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:5415)
      at devguide.advanced.refcursor.TestClient.main(TestClient.java:21)
      Detail 0 ##
      java.sql.SQLException: Closed Statement: next
      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:73)
      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:111)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:174)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:230)
      at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:394)
      at devguide.advanced.refcursor.RequestForTechImpl.hasNextForCollection(RequestForTechImpl.java:109)
      at devguide.advanced.refcursor.RequestForTechImpl.storeNewResultSet(RequestForTechImpl.java:207)
      at devguide.advanced.refcursor.RequestForTechImpl.executeQueryForCollection(RequestForTechImpl.java:51)
      at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:1010)
      at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:1160)
      at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:1080)
      at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:1074)
      at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:5415)
      at devguide.advanced.refcursor.TestClient.main(TestClient.java:21)
      Process exited with exit code 1.

      The same application works in 10.1.3.3 without any error.

      Is there something different in the new version (11g TP4) ?

      thanks in advance,
      Bahar
        • 1. Re: TP4 - View Object with REF CURSOR - SQLException
          Frank Nimphius-Oracle
          Hi,

          please try a workspace that you built in JDeveloper 11. TP4 might have issues when migrating 10.1.3 projects. So please re-build the sample and try again.

          Frank
          • 2. Re: TP4 - View Object with REF CURSOR - SQLException
            Bahar
            Hi,

            I have built another workspace in 11g TP4 and result was the same.

            xception in thread "main" oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.sql.SQLException, msg=Closed Statement: next
                 at adv.vo.refcursor.RequestsForTechImpl.hasNextForCollection(RequestsForTechImpl.java:71)

            to test the function call, created a method in ApplicationModule and test it. The same exception has been thrown when I loop over the result set. below is exception:

            Exception in thread "main" oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.sql.SQLException, msg=Closed Statement: next
                 at adv.vo.refcursor.TestClientFunction.main(TestClientFunction.java:34)
            ## Detail 0 ##
            java.sql.SQLException: Closed Statement: next
                 at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:73)
                 at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:111)
                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:174)
                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:230)
                 at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:394)
                 at adv.vo.refcursor.TestClientFunction.main(TestClientFunction.java:27)
            Process exited with exit code 1.

            If another function e.g. returning varchar2 was called, there was no exception and return value was given.

            method in the AM is :
            public ResultSet getRequestsForTechProc(String email) {
            ResultSet rs = (ResultSet) callStoredFunction(OracleTypes.CURSOR,
            "RefCursorExample.get_requests_for_tech(?)",
            new Object[] { email });
            return rs;
            }

            regards,
            Bahar
            • 3. Re: TP4 - View Object with REF CURSOR - SQLException
              Bahar
              Hi,

              I changed the callStoredFunction method and removed statement close part, then it worked.
              This method is working for VARCHAR return values with statement close part uncommented.

              However, isn't it necessary to close statement after getting the result set?

              Is it correct to leave statement open for view objects like that?


              The following is the callStoredFunction method:

              protected Object callStoredFunction(int sqlReturnType, String stmt,
              Object[] bindVars) {
              CallableStatement st = null;
              try {
              // 1. Create a JDBC CallabledStatement
              st = getDBTransaction().createCallableStatement(
              "begin ? := "+stmt+";end;",0);
              // 2. Register the first bind variable for the return value
              st.registerOutParameter(1, sqlReturnType);
              if (bindVars != null) {
              // 3. Loop over values for the bind variables passed in, if any
              for (int z = 0; z < bindVars.length; z++) {
              // 4. Set the value of user-supplied bind vars in the stmt
              st.setObject(z + 2, bindVars[z]);
              }
              }
              // 5. Set the value of user-supplied bind vars in the stmt
              st.executeUpdate();
              // 6. Return the value of the first bind variable
              return st.getObject(1);
              }
              catch (SQLException e) {
              throw new JboException(e);
              }
              *// finally {*
              *// if (st != null) {*
              *// try {*
              *// // 7. Close the statement*
              *// st.close();*
              *// }*
              *// catch (SQLException e) {}*
              *// }*
              *// }*
              }

              thank you in advance,

              Bahar