Stored procederes with out parameters not working under oracle 11 + hibernate 5.0.11 [DATAJPA-996]

[spring-projects-issues]

alephx opened DATAJPA-996 and commented

After upgrading spring boot from 1.3 to 1.4 and hibernate from 4 to 5, stored procedure working earlier, now returns "PLS-00306: wrong number of types of arguments in call to YYYY".

Code is exactly the same as in reference documentation (5.4. Stored procedures), (only difference beside procedure and parameters names is other type of parameters - String instead of Integer ).

Little debugging shows change in hibernate 5 to using named parameters instead of position indexed as in 4.
But Spring Data JPA for returning out parameter passes name "out"
static final String SYNTHETIC_OUTPUT_PARAMETER_NAME = "out"
which differs from real procedure out parameter name, and as I guess is cause of error...

---

Affects: 1.10.4 (Hopper SR4)

2 votes, 5 watchers

[spring-projects-issues]

alephx commented

Changing in Repository from

@Procedure(procedureName = "abc")
 String proc(@Param("p1") String p1);

to

@Procedure(procedureName = "abc")
String proc(String p1);

solves problem (forces hibernate to not using named parameters).

[spring-projects-issues]

Igor Milina commented

This is causing us a problem as well, and I have confirmed that invalid output parameter name is causing call to fail. Bug is present only when specifying procedure name directly on annotation @Procedure(procedureName = "abc"), not when using procedure defined using @NamedStoredProcedureQuery. The problem seems to be in StoredProcedureAttributeSource class which will always create StoredProcedureAttributes with outputParameterName=null (which will then default to SYNTHETIC_OUTPUT_PARAMETER_NAME), unless using named stored proc.

Why can't StoredProcedureAttributeSource use "outputParameterName" attribute of @Procedure(procedureName = "...", outputParameterName = "...") annotation, if present?

[spring-projects-issues]

Kyle Anderson commented

I ran into this issue too. The work around suggested by @alephx worked for me. Many thanks for that.

I hope to see this issue resolved

[spring-projects-issues]

Igor Milina commented

The workaround of using positional parameters is fragile, to say the least. Reordering parameters on java method or on the database procedure will cause code to fail.

I think this issue should have higher priority than 'minor'. Currently, calling stored procedure with return value is possible only using @NamedStoredProcedureQuery. And to make things worse, if you use Eclipselink as persistence provider, you cannot event use @NamedStoredProcedureQuery because of this bug: https://bugs.eclipse.org/bugs/show_bug.cgi?id=440078 which I don't think will ever be resolved... The real workaround is not to use annotations at all, but to use programmatic approach with createStoredProcedureQuery() method on EntityManager

[spring-projects-issues]

Andrew Spencer commented

A little addition to the workaround from alephx, if your procedure has an output variable then you need to remove outputParameterName parameter on @Procedure as well as removing the annotations on the input parameters.

Suppose your procedure's output parameter is named "retmsg". The following will fail, because the name OUT will be used for the output parameter:

@Procedure(procedureName = "abc", outputParameterName = "retmsg")
String proc(String p1);

This will work:

@Procedure(procedureName = "abc")
String proc(String p1);

 

[mp911de]

Is this still an issue?