8 Replies Latest reply: Aug 9, 2012 11:29 AM by rp0428 RSS

    java.sql.SQLException: statement handle not executed

    gg24

      hello,

      i am calling a stored procedure and its returns a REF CURSOR and i am getting intermittent exceptions below,

      org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [{call
      xxxx_pkg(?,?)}]; SQL state [99999]; error code [17144]; statement handle not executed; nested exception is java.sql.SQLException: statement handle not executed


      and

      org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [{call
      xxxx_pkg(?,?,?)}]; SQL state [99999]; error code [17009]; Closed Statement; nested exception is java.sql.SQLException: Closed Statement



      any clue what could be the issue,

      Regards
      GG

        • 1. Re: java.sql.SQLException: statement handle not executed
          rp0428
          >
          am calling a stored procedure and its returns a REF CURSOR and i am getting intermittent exceptions below
          . . .
          any clue what could be the issue,
          >
          Not based on what you posted.

          What do you mean 'intermittent'? Do you always get both? Do you get them in the same order you posted them?

          Can you call the procedure manually using sql*plus?

          What is the 4 digit version of Oracle (result of SELECT * FROM V$VERSION)? What is the jdk version and JDBC jar version?

          Are you sure the connection and statement are open when the call is made?
          • 2. Re: java.sql.SQLException: statement handle not executed
            gg24
            thanks for your response, by the way first time when i call i get the results but 2nd call onwards i am getting below error,

            org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [{call
            xxxx_pkg(?,?)}]; SQL state [99999]; error code [17144]; statement handle not executed; nested exception is java.sql.SQLException: statement handle not executed


            sorry i forgot to add before that i am getting below error when i call 2nd stored procedure that's returning a ref cursor also, so its the same behavior(1st call fine)

            org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [{call
            xxxx_pkg(?,?,?)}]; SQL state [99999]; error code [17009]; Closed Statement; nested exception is java.sql.SQLException: Closed Statement

            by the way java version is 1.6 and using ojdbc14 is the jdbc jar version, we have Hibernate in the middle. not sure if the ojdbc14.jar can be used for Oracle 12g or 10g
            • 3. Re: java.sql.SQLException: statement handle not executed
              rp0428
              >
              thanks for your response, by the way first time when i call i get the results but 2nd call onwards i am getting below error,
              >
              If it works the first time but you get exceptions the next time chances are the statement or connection is being closed. The one error even says it is for a 'Closed Statement'.
              >
              by the way java version is 1.6 and using ojdbc14 is the jdbc jar version, we have Hibernate in the middle. not sure if the ojdbc14.jar can be used for Oracle 12g or 10g
              >
              There is no 12g and you didn't answer the other questions
              >
              What is the 4 digit version of Oracle (result of SELECT * FROM V$VERSION)? What is the jdk version and JDBC jar version?

              Are you sure the connection and statement are open when the call is made?
              Here is the official Oracle JDBC page that shows the Oracle DB versions supported for each of the JDBC drivers available and also describes the JDBC jars and what JDK versions they support.
              http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#02_01
              • 4. Re: java.sql.SQLException: statement handle not executed
                EJP
                nested exception is java.sql.SQLException: Closed Statement
                any clue what could be the issue,
                You have attempted to execute a query with a Statement you have already closed, or whose Connection you have already closed.
                • 5. Re: java.sql.SQLException: statement handle not executed
                  gg24
                  thanks ...by the way i have other calls to different stored procedures(returning ref cursors) and i am not seeing any issues with those, but only with these two. one more thing i would like to add that these 2 procedures internally has insert or update statements before returning a ref cursors. this could be an issue ?
                  • 6. Re: java.sql.SQLException: statement handle not executed
                    EJP
                    Anything could be an issue, it's pointless to speculate further until you post some code.
                    • 7. Re: java.sql.SQLException: statement handle not executed
                      gg24
                      its pretty simple have a java class calling hibernateTemplate's findByNamedQueryAndNamedParam method by passing the procedure name and binding parameters/values, and here is the stack

                      org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [{call
                      xxx_pkg(?,?)}]; SQL state [99999]; error code [17144]; statement handle not executed; nested exception is java.sql.SQLException: statement handle not executed
                      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
                      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
                      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
                      at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
                      at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
                      at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
                      at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
                      at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQueryAndNamedParam(HibernateTemplate.java:1006)

                      Caused by: java.sql.SQLException: statement handle not executed
                      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
                      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
                      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:403)
                      at oracle.jdbc.driver.T4CStatement.doDescribe(T4CStatement.java:701)
                      at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3355)
                      at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2009)
                      at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494)
                      at org.hibernate.type.StringType.get(StringType.java:18)
                      at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
                      at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
                      at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
                      at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2091)
                      at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)
                      at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308)
                      at org.hibernate.loader.Loader.getRow(Loader.java:1206)
                      at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)
                      at org.hibernate.loader.Loader.doQuery(Loader.java:701)
                      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
                      at org.hibernate.loader.Loader.doList(Loader.java:2217)
                      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2108)
                      at org.hibernate.loader.Loader.list(Loader.java:2103)
                      at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
                      at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1696)
                      at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
                      at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
                      at org.springframework.orm.hibernate3.HibernateTemplate$34.doInHibernate(HibernateTemplate.java:1015)
                      at org.springframework.orm.hibernate3.HibernateTemplate$34.doInHibernate(HibernateTemplate.java:1)
                      at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
                      • 8. Re: java.sql.SQLException: statement handle not executed
                        rp0428
                        >
                        its pretty simple have a java class calling hibernateTemplate's findByNamedQueryAndNamedParam method by passing the procedure name and binding parameters/values
                        >
                        Since it is so simple why haven't you posted the code as requested?