5

Using:

  1. Spring Boot 1.4.0.RELEASE
  2. JPA : 2.1
  3. Hibernate : 5.0.9
  4. Hibernate Dialect : org.hibernate.dialect.Oracle10gDialect
  5. Oracle Database : 12.1.0.2
  6. Oracle JDBC: ojdbc7 12.1.3-0-0

In essence, when I try to execute a Stored Procedure, I'm faced with this error:

operation not allowed: Ordinal binding and Named binding cannot be combined!

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", Integer.class, ParameterMode.OUT)
    .setParameter("param1", request.getTransactiontypeId());
    sp.execute();

Turning on SQL debug output, it reveals the generated sql call as {call mySp(?,?)}.

That seems...dodgy (as it contains no hint of the specified names), but I understand it might just be hibernate doing internal translations?

IF I change the stored proc setup to use positional parameters instead, things actually work, but I would really prefer to used named parameters.

Stored proc spec:

PROCEDURE mySp (param1 IN tdocs.transactiontype_id%TYPE,
outParam OUT tdocs.doc_id%TYPE );

demaniak
  • 3,716
  • 1
  • 29
  • 34
  • Could you add the spec of the PL/SQL procedure `mySp()` to your question? – Martin Schapendonk Aug 31 '16 at 13:08
  • the generated sql is odd. Oracle does not support question marks as bind variable placeholders. I looks like something is also happening on JDBC level. – ibre5041 Aug 31 '16 at 13:40
  • @MartinSchapendonk SP spec added, for what it may be worth. – demaniak Aug 31 '16 at 18:48
  • @ibre5041 that seems pretty normal for logging. Still not sure what's going on here. Spec matches with code imho. Google search only suggests that replacing with ordinal binds works *sigh* – Martin Schapendonk Aug 31 '16 at 18:54
  • @demaniak, did you solve tihs problem? I'm getting the same error. – Fabricio Koch Nov 14 '16 at 17:06
  • @FabricioKoch sadly I did not. The best I could do was use ordinal binding. The very strange thing was: in the same project, we used many SP's, and with some of them the named bindings worked fine. I have NO idea what the difference was between "working" and "not working" situations. – demaniak Nov 14 '16 at 19:49
  • @demaniak how is this ordinal binding? I know just this way: `sp.setParameter("name", value);` I'm sorry if it's a basic question, but I new to Java world. – Fabricio Koch Nov 14 '16 at 21:49
  • @FabricioKoch basically you map parameters based on position, not name. For example, if you define your query as `{call someSP(?)}`, then you would set the parameter value with `q.setParameter(1, "1");`, where `q` is a `Query` instance. If you have many parameters, this can become a maintenace/debugging nightmare, which is why it is not preferred. But, if you have no choice... – demaniak Nov 15 '16 at 16:09
  • @demaniak I understand it now. But how can I retrieve the output parameter? I couldn't find an example without the named parameter (that doesn't work) – Fabricio Koch Nov 16 '16 at 16:08
  • @demaniak got it! I change to use `registerStoredProcedureParameter`. I was mapping the parameters. – Fabricio Koch Nov 16 '16 at 16:18
  • I am facing exactly the same problem, my guts tell me that org.hibernate.dialect.Oracle10gDialect may be the fault, but I cannot upgrade to org.hibernate.dialect.Oracle12cDialect because my database is Oracle11g. @demaniak did you try to change that dialect? – Mihai Cicu Feb 27 '17 at 10:17
  • Hi @MihaiCicu, unfortunately I can honestly not remember if I tried to force a newer dialect. I do remember LOOKING at that, since I was also convinced the dialect had to be involved in the problem. Definitely worth a try! – demaniak Mar 01 '17 at 11:07
  • 1
    @demaniak well somehow the problem seems to be gone after upgrading to spring boot 1.5.1, hibernate 5.2.8, and switching from the ojdbc6 to 7, whilst usign the same dialect (10g) and the named parameters :) – Mihai Cicu Mar 01 '17 at 11:25

1 Answers1

1

This seems to be a bug in Hibernate 5.0.9. For me, switching to Hibernate 5.0.11.Final did the trick, with no other changes.

Thanks to Mihai Cicu for pointing in that direction in the comments.

lbilger
  • 304
  • 1
  • 8