Java – the problem of “sequence binding and naming binding” occurs when calling stored procedures with JPA 2.1

use:

> Spring Boot 1.4. 0. Release > JPA: 2.1 > sleep: 5.0 9 > hibernate Dialect: org hibernate. dialect. Oracle10gdialect > Oracle Database: 12.1 0.2 > Oracle JDBC：ojdbc7 12.1. 3-0-0

In essence, when I try to execute a stored procedure, I encounter this error:

Operation not allowed: serial number binding and named binding cannot be merged!

The full stack trace is as follows:

2016-08-31 13:35:37.906+0200 | APP | WARN  | MvcAsync1 | o.h.e.j.s.sqlExceptionHelper | sql Error: 17090,sqlState: 99999
2016-08-31 13:35:37.907+0200 | APP | ERROR | MvcAsync1 | o.h.e.j.s.sqlExceptionHelper | operation not allowed: Ordinal binding and Named binding cannot be combined!
2016-08-31 13:35:37.909+0200 | APP | ERROR | http-nio-8081-exec-3 | o.a.c.c.C.[.[.[.[dispatcherServlet] | Servlet.service() for servlet dispatcherServlet threw exception
java.sql.sqlException: operation not allowed: Ordinal binding and Named binding cannot be combined!
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5626)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
    at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:52)
    at org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(ProcedureOutputsImpl.java:32)
    at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:411)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:363)
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.outputs(StoredProcedureQueryImpl.java:234)
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:217)
    at com.mycomp.services.DocumenServiceImpl.addNewDoc(DocumentServiceImpl.java:88)
    at com.mycomp.backend.rest.CreateController.lambda$0(CreateController.java:39)
    at org.springframework.web.context.request.async.WebAsyncManager$4.run(WebAsyncManager.java:316)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.lang.Thread.run(Thread.java:745)

The calling code is as follows:

StoredProcedureQuery sp = em.createStoredProcedureQuery("mySp")
    .registerStoredProcedureParameter("param1",Integer.class,ParameterMode.IN)                
    .registerStoredProcedureParameter("outParam",ParameterMode.OUT)
    .setParameter("param1",request.getTransactiontypeId());
    sp.execute();

Open the SQL debugging output, which displays the generated SQL call as {call mysp (?,?)}

It seems... Cunning (because it doesn't contain any hint of the specified name), but I know it may just be hibernate for internal translation?

If I change the stored procedure settings to use positional parameters, things actually work, but I really prefer to use named parameters

Stored procedure specification:

Program mysp (Param1 in tdocs.transactiontype_id% type, outparam out tdocs. Doc_id% type);

Solution

This seems to be hibernate 5.0 An error in 9 For me, switch to hibernate 5.0 11. Final did the trick, but there was no other change

Thank Mihai CICU for pointing out this direction in the comments