I’m using WildFly 16.0.0.Final, Oracle 12c, Hibernate 5.3.9.Final and Oracle JDBC 18.3.0.0.
Every once in a while all my JPA Stored Procedures will start to fail with the error below:
{quote}Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults
at java.base/java.lang.Thread.run(Thread.java:834) \[java.base:]
at org.jboss.threads@2.3.3.Final//org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1377)
at org.jboss.as.jpa@16.0.0.Final//org.jboss.as.jpa.container.StoredProcedureQueryNonTxInvocationDetacher.execute(StoredProcedureQueryNonTxInvocationDetacher.java:248) \[wildfly-jpa-16.0.0.Final.jar:16.0.0.Final]
at org.hibernate@5.3.9.Final//org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) \[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
...
Caused by: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults
... 73 more
at org.hibernate@5.3.9.Final//org.hibernate.result.internal.OutputsImpl.convert(OutputsImpl.java:83) \[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate@5.3.9.Final//org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) \[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate@5.3.9.Final//org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:60) \[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate@5.3.9.Final//org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:415) \[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate@5.3.9.Final//org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(ProcedureOutputsImpl.java:34) \[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate@5.3.9.Final//org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:351) \[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate@5.3.9.Final//org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:631) \[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate@5.3.9.Final//org.hibernate.procedure.internal.ProcedureCallImpl.execute(ProcedureCallImpl.java:614) \[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
... 94 more
Caused by: java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined\!
at com.oracle@18.3.0.0.0//oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1079) \[ojdbc8-18.3.0.0.jar:18.3.0.0.0]
at org.jboss.ironjacamar.jdbcadapters@1.4.12.Final//org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:442)
at com.oracle@18.3.0.0.0//oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4239) \[ojdbc8-18.3.0.0.jar:18.3.0.0.0]{quote}
It seems that Hibernate doesn’t really uses the named parameters. Instead, it relies on the order the parameters are declared, and the order should match the order declared on the Oracle Stored Procedure.
The failing stored procedures all have the following format:
{code:sql}CREATE OR REPLACE PROCEDURE PR_TEST(cursor_param OUT SYS_REFCURSOR, in_param1 IN VARCHAR2, in_param2 IN VARCHAR2, out_param1 OUT VARCHAR2, out_param2 OUT VARCHAR2);{code}
{code:java}var storedProcedure = em.createStoredProcedureQuery("PR_TEST");
storedProcedure.registerStoredProcedureParameter("cursor_param", Class.class, ParameterMode.REF_CURSOR);
storedProcedure.registerStoredProcedureParameter("in_param1", String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("in_param2", String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("out_param1", String.class, ParameterMode.OUT);
storedProcedure.registerStoredProcedureParameter("out_param2", String.class, ParameterMode.OUT);
...
storedProcedure.execute();{code}
|
|
