StoredProcedureQuery with OUT param fails with Oracle when using named parameters

Description

... javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:224) at support.hibernate.entity.TestHibernate.test(TestHibernate.java:76) ... 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:109) at org.hibernate.result.internal.OutputsImpl.convert(OutputsImpl.java:79) at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:56) 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) ... 32 more Caused by: java.sql.SQLException: The number of parameter names does not match the number of registered praremeters at oracle.jdbc.driver.OracleSql.setNamedParameters(OracleSql.java:199) at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4753) at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378) at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:52) ...

Have also seen a variant exception (from Oracle code):

java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!

Simple stored procedure with a single input and single output parameter:

create or replace PROCEDURE TEST_PROC(param1 CHAR, retval OUT INTEGER) AS BEGIN retval:=1; END TEST_PROC;

final StoredProcedureQuery query = entityManager.createStoredProcedureQuery("TEST_PROC"); query.registerStoredProcedureParameter("param1", String.class, ParameterMode.IN); query.registerStoredProcedureParameter("retval", Integer.class, ParameterMode.OUT); query.setParameter("param1", "test"); final Integer retval = (Integer) query.getOutputParameterValue("retval"); // this fails

Seems to have been introduced in 5.0.8.

Web links

Activity

Show:

Olegs Sedacs July 14, 2016 at 11:07 AM

PROCEDURE :

PROCEDURE save_coded_data
(
in_hash VARCHAR2,
in_data VARCHAR2,
in_masked VARCHAR2,
out_result OUT INTEGER
)
AS
l_sk INTEGER;
BEGIN
SELECT COUNT INTO l_sk FROM coded_data WHERE hash = in_hash;
IF l_sk = 0 THEN
INSERT INTO coded_data
(
hash,
data,
masked
)
VALUES
(
in_hash,
in_data,
in_masked
);
ELSE
raise_application_error(-20000, 'Such card already is registered!');
– UPDATE coded_data SET
– data = in_data,
– masked = in_masked
– WHERE hash = in_hash;
END IF;
out_result := l_sk + 1;
END save_coded_data;
END CD_PAYM_PROCESING_PKG;

Olegs Sedacs July 14, 2016 at 11:06 AM

Hibernate + Oracle 11g
Dependecies :
Hibernate 5.1.0
com.oracle:ojdbc6:11.2.0.3.0
Spring boot 1.3.6

I have procedure with 2 in params and 1 out - it works;

But another procedure with 3 in params and 1 out param don't works (if use named params)
If use positioned params, then it works;
Here is code :

@NamedStoredProcedureQuery(
name="save_coded_data",
procedureName="FSC_PAYMENTS.CD_PAYM_PROCESING_PKG.save_coded_data",
parameters={
@StoredProcedureParameter(name="in_hash", type=String.class, mode= ParameterMode.IN),
@StoredProcedureParameter(name="in_data", type=String.class, mode= ParameterMode.IN),
@StoredProcedureParameter(name="in_masked", type=String.class, mode= ParameterMode.IN),
@StoredProcedureParameter(name="out_result", type=Integer.class, mode= ParameterMode.OUT)
}
)

usege :

return (int) em
.createNamedStoredProcedureQuery("save_coded_data")
.setParameter("in_hash", "value")
.setParameter("in_data", "value")
.setParameter("in_masked", "value")
.getOutputParameterValue("out_result");

Stacktrace :
org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
at org.hibernate.result.internal.OutputsImpl.convert(OutputsImpl.java:79) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:56) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
at org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(ProcedureOutputsImpl.java:32) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:420) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:372) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.outputs(StoredProcedureQueryImpl.java:240) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:279) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]
at com.fsc.payment.card.jpa.repository.whitelabel.WLSettingRepositoryTest.proc(WLSettingRepositoryTest.java:53) ~[test/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_74]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_74]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_74]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_74]
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47) [junit-4.11.jar:na]
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) [junit-4.11.jar:na]
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44) [junit-4.11.jar:na]
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) [junit-4.11.jar:na]
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75) [spring-test-4.2.7.RELEASE.jar:4.2.7.RELEASE]
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) [spring-test-4.2.7.RELEASE.jar:4.2.7.RELEASE]
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) [spring-test-4.2.7.RELEASE.jar:4.2.7.RELEASE]
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271) [junit-4.11.jar:na]
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254) [spring-test-4.2.7.RELEASE.jar:4.2.7.RELEASE]
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89) [spring-test-4.2.7.RELEASE.jar:4.2.7.RELEASE]
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238) [junit-4.11.jar:na]
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63) [junit-4.11.jar:na]
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236) [junit-4.11.jar:na]
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53) [junit-4.11.jar:na]
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229) [junit-4.11.jar:na]
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) [spring-test-4.2.7.RELEASE.jar:4.2.7.RELEASE]
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) [spring-test-4.2.7.RELEASE.jar:4.2.7.RELEASE]
at org.junit.runners.ParentRunner.run(ParentRunner.java:309) [junit-4.11.jar:na]
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193) [spring-test-4.2.7.RELEASE.jar:4.2.7.RELEASE]
at org.junit.runner.JUnitCore.run(JUnitCore.java:160) [junit-4.11.jar:na]
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:119) [junit-rt.jar:na]
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42) [junit-rt.jar:na]
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234) [junit-rt.jar:na]
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74) [junit-rt.jar:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_74]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_74]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_74]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_74]
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144) [idea_rt.jar:na]
Caused by: java.sql.SQLException: The number of parameter names does not match the number of registered praremeters
at oracle.jdbc.driver.OracleSql.setNamedParameters(OracleSql.java:198) ~[ojdbc6-11.2.0.3.0.jar:11.2.0.3.0]
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4712) ~[ojdbc6-11.2.0.3.0.jar:11.2.0.3.0]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376) ~[ojdbc6-11.2.0.3.0.jar:11.2.0.3.0]
at com.jolbox.bonecp.PreparedStatementHandle.execute(PreparedStatementHandle.java:140) ~[bonecp-0.8.0.RELEASE.jar:na]
at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:52) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
... 39 common frames omitted

Former user May 25, 2016 at 12:46 AM

Fixed in master, 5.1, and 5.0 branches.

Fixed

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

Created May 18, 2016 at 7:08 PM
Updated July 14, 2016 at 11:07 AM
Resolved May 25, 2016 at 12:46 AM