StoredProcedureQuery with OUT param fails with Oracle when using named parameters

Description

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;

Seems to have been introduced in 5.0.8.

Environment

Oracle

Activity

Show:
Gail Badner
May 25, 2016, 12:46 AM

Fixed in master, 5.1, and 5.0 branches.

Olegs Sedacs
July 14, 2016, 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

Olegs Sedacs
July 14, 2016, 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;

Fixed

Assignee

Gail Badner

Reporter

Stephen Fikes

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure