CODEWITHSUNDEEP
javahibernatejpaspring-data-jpaspring-data
pjj
pjj

Reputation: 2135

CrudRepository - Stored Procedure call is not working because of type/number of argument issue

I have JPA entity as

enter image description here

And then Repository as

enter image description here

Now, when I run it then I get following exception:

enter image description here

And stored procedure is:

enter image description here

It is running against Oracle database. Can someone please help me understand even though I have correct parameter numbers and type, still why I am getting this exception.

Please note: I do not have local environment so I cannot put a sample code, and please do not worry about class/method name, I tried to camouflage them so they may be inconsistent.

There is one more question that suppose I have 2 OUT parameters then how you I create my entity class, with one output parameter I know I can return String (or appropriate return type) but in case of 2 OUT parameters I do no know how to do it? I have read this article but it is only with 1 OUT parameter, and I couldn't find any article or post which explains for 2 OUT parameter. If someone has a code with 2 OUT parameter then it would be helpful.

Upvotes: 1

Views: 1991

Answers (1)

xerx593
xerx593

Reputation: 13261

Please try:

  • use the exact (db) names of procedure parameters:

    @StoredProcedureParameter(name = "tbl_name" ...
@StoredProcedureParameter(name = "p_date" ...
@StoredProcedureParameter(name = "p_message" ...

  • or (alternatively) omit names completely (rely on position).

From StoredProcedureParameter javadoc:

The name of the parameter as defined by the stored procedure in the database. If a name is not specified, it is assumed that the stored procedure uses positional parameters.

Currently you can't have multiple OUT-parameters using spring-data, but (should be) no problem with standard JPA:

StoredProcedureQuery spq = em.createNamedStoredProcedureQuery("my_proc");
proc.setParameter("p_in", 1);
proc.execute();
Integer res1 = (Integer) proc.getOutputParameterValue("out1");
Integer res2 = (Integer) proc.getOutputParameterValue("out2");

see also: Spring Data JPA NamedStoredProcedureQuery Multiple Out Parameters

Upvotes: 2

Related Questions