title: JPA / Hibernate : call stored procedure with input and output parameters
body: <p>I have a problem to call a sorted procedure in Java with JPA</p>
  
  <p><strong>Here is the code Java :</strong></p>
  
  <pre><code>    StoredProcedureQuery storedProcedureQuery = getEntityManager().createNamedStoredProcedureQuery("RECPOCH");
  
  storedProcedureQuery.setParameter(Pochette.SP_RECPOCH_PARAM_STRSTARTSIM, pochetteCriteria.getDebutNumPochette());
  storedProcedureQuery.setParameter(Pochette.SP_RECPOCH_PARAM_STRLASTSIM, pochetteCriteria.getFinNumPochette());
  storedProcedureQuery.setParameter(Pochette.SP_RECPOCH_PARAM_NACTELID, pochetteCriteria.getActelId());
  
  storedProcedureQuery.execute();
  Integer result = (Integer) storedProcedureQuery.getOutputParameterValue(Pochette.SP_RECPOCH_PARAM_NRESULT);
  
  return Long.valueOf(result);
  </code></pre>
  
  <p><strong>Here is the code Oracle :</strong> </p>
  
  <pre><code>"RECPOCH" ( NACTELID IN NUMBER, STRSTARTSIM IN VARCHAR, STRLASTSIM IN VARCHAR, NRESULT OUT INTEGER )
  </code></pre>
  
  <p><strong>Trace Exception :</strong>
  <code><pre>13:24:40.295 [http-nio-8080-exec-6] DEBUG o.s.orm.jpa.JpaTransactionManager - Participating in existing transaction
  Hibernate: {call RECPOCH(?,?,?,?)}
  javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults
      at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:230)
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      at java.lang.Thread.run(Thread.java:748)
  Caused by: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults
      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
      at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:223)
      ... 90 more
  Caused by: java.sql.SQLException: Le nombre de noms de paramètre ne concorde pas avec celui des paramètres inscrits
      at oracle.jdbc.driver.OracleSql.setNamedParameters(OracleSql.java:198)
      at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:197)
  13:24:40.833 [http-nio-8080-exec-6] DEBUG o.s.orm.jpa.JpaTransactionManager - Initiating transaction commit
      at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:197)
  13:24:40.833 [http-nio-8080-exec-6] DEBUG o.s.orm.jpa.JpaTransactionManager - Committing JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@6db6eb5c]
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
      at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java)
      at org.hibernate.result.internal.OutputsImpl.(OutputsImpl.java:52)
      ... 95 more
  </pre></code></p>
  
  <p><strong>Named Sorted procedure definition in entity :</strong> 
  <code><pre>    @NamedStoredProcedureQueries({
              @NamedStoredProcedureQuery(name = Pochette.SP_RECPOCH_NAME, procedureName = "RECPOCH", parameters = {
                      @StoredProcedureParameter(name = Pochette.SP_RECPOCH_PARAM_NRESULT, mode = ParameterMode.OUT, type = Long.class),
                      @StoredProcedureParameter(name = Pochette.SP_RECPOCH_PARAM_STRSTARTSIM, mode = ParameterMode.IN, type = String.class),
                      @StoredProcedureParameter(name = Pochette.SP_RECPOCH_PARAM_STRLASTSIM, mode = ParameterMode.IN, type = String.class),
                      @StoredProcedureParameter(name = Pochette.SP_RECPOCH_PARAM_NACTELID, mode = ParameterMode.IN, type = Long.class)
              })
      })</pre></code>
  Do you have an idea, please ?</p>