JPA StoredProcedureQuery intermitent fail with named parameters

Description

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:

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]

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:

 

 

Environment

Oracle

Activity

Show:
CARTEREAU Benjamin
December 1, 2020, 10:20 AM
Edited

Hello,

I think we are facing the exact same issue with an older version of Hibernate (5.1.10).
It can just fail after some days working smoothly.

Here is an example of the call :

We use an SqlResultSetMapping as output:

And the procedure is declared that way within Oracle DB :

I don’t know if, in any way, it could be related to the bug/fix of EclipseLink:

@Fabio C. Martins have you any news about this bug? Have you found a workaround?

Best regards

Assignee

Unassigned

Reporter

Fábio C. Martins

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure