Correctly call a stored procedure using Spring Data JPA

Question

I'm having troubles trying to call a stored procedure from a Spring data repository. Following Spring data documentation and several answers here on SO this seems to be the correct way, but I keep having this error:

PLS-00306: wrong number or types of arguments in call to 'GET_DESCR_BDD_BDS'

This is the stored procedure signature

procedure GET_DESCR_BDD_BDS(PRGPVV in number,
                                COD_SEZ in number,
                                FL_BDD_BDS in number,
                                prg_doc out varchar2,
                                repo_pos out number
                                )

And this is how i have implemented the call (I may have messed things up a bit in the different attempts to make things work)
Model

@NamedStoredProcedureQuery(name = "DescrBddBds.descr", 
    procedureName = "PRK_BDD.GET_DESCR_BDD_BDS",
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "PRGPVV", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "COD_SEZ", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "FL_BDD_BDS", type = Integer.class)
        ,
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "prg_doc", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "repo_pos", type = Integer.class)
    },
    resultClasses = DescrBddBds.class
)
@Entity
public class DescrBddBds implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = -2182033603838684233L;
    @Id
    @Column(name = "prg_doc")
    private String prgDoc;
    @Column(name = "repo_pos")
    private Integer repoPos;

    public String getPrgDoc() {
        return prgDoc;
    }
    public void setPrgDoc(String prgDoc) {
        this.prgDoc = prgDoc;
    }
    public Integer getRepoPos() {
        return repoPos;
    }
    public void setPepoPos(Integer repoPos) {
        this.repoPos = repoPos;
    }

}

Repository

@Repository
public interface HtmlProceduresRepo extends CrudRepository<DescrBddBds, String> {

    @Procedure(name = "descr", procedureName="PRK_BDD.GET_DESCR_BDD_BDS")
    DescrBddBds descr(@Param("PRGPVV") Integer codiceDoc, @Param("COD_SEZ") Integer sezione, @Param("FL_BDD_BDS") Integer flagBddBds);
}

Calling the procedure from SQL Developer with the same user i call it from the application works just fine

var b number;
var d number;
var e number;
exec :b:= 1;
exec :d:= 2;
exec :e:= 3;
execute PRK_BDD.GET_DESCR_BDD_BDS(:b, :d, :e, :out_param1, :out_param2);
print out_param1;
print out_param2;

Can you check whether you have access to run this stored procedure with the user you are using in database. — Prasad, Mar 01 '17 at 12:41
executable access? I faced the issue many times and this is only because of the access permission to the current user i was using. — Prasad, Mar 01 '17 at 12:46
If I call the stored procedure using SQL Developer it works just fine. Also shouldn't i receive a different error than the one i posted in my question if it was a permission issue? — valepu, Mar 01 '17 at 12:47
Because the number arguments are correct and you are setting the 'IN' parameter values correctly so it may be the issue only because of permission. — Prasad, Mar 01 '17 at 12:49
See my edit (tried calling the procedure and it works fine). I have the feeling Spring Data JPA is ignoring the namedstoredprocedure annotation, when i used @Procedure(name = "descr") or @Procedure(name = "DescrBddBds.descr") it tried to look for a field "descr" in the model rather than calling the stored procedure... — valepu, Mar 01 '17 at 13:04
Hell, You can have a look [here](https://stackoverflow.com/questions/3572626/how-to-call-a-stored-procedure-from-java-and-jpa/70788256#70788256), I hope it helps : [How to call a stored procedure from Java and JPA SQL Server](https://stackoverflow.com/questions/3572626/how-to-call-a-stored-procedure-from-java-and-jpa/70788256#70788256) — RED-ONE, Jun 05 '23 at 22:50
@RED-ONE when I wrote this question Spring Data had no support multiple output parameters stored procedures, it was added 3 years later in version 2.2 (as you can see from my answer below) so we had to do things differently in my project back then. Thanks for the comment though — valepu, Jun 06 '23 at 08:34

valepu · Accepted Answer · 2020-07-03T10:29:00.527

1

In the end i have discovered that Spring Data JPA does not currently support stored procedures with multiple output parameters. It's an open issue on the project and it doesn't appear to be any progress on that since it was opened 2 years ago

https://github.com/spring-projects/spring-data-examples/issues/80

https://jira.spring.io/browse/DATAJPA-707

https://jira.spring.io/browse/DATAJPA-748

EDIT: It looks like the issue has been resolved on version 2.2 RC1

edited Jul 03 '20 at 10:29

answered Mar 20 '17 at 15:32

valepu

3,136
7
36
67

They just posted a pull request for this issue – valepu Oct 19 '18 at 17:07

score 1 · Answer 2 · answered Mar 28 '20 at 01:35

Updated solution with multiple OUT parameters, see.

@NamedStoredProcedureQueries({ //
        @NamedStoredProcedureQuery(name = "User.plus1", procedureName = "plus1inout",
                parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
                        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res", type = Integer.class) }), //
        @NamedStoredProcedureQuery(name = "User.plus1IO2", procedureName = "plus1inout2",
                parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
                        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res", type = Integer.class),
                        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res2", type = Integer.class) }), //
        @NamedStoredProcedureQuery(name = "User.plus1IOoptional", procedureName = "plus1inoutoptional",
                parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class),
                        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res", type = Integer.class),
                        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res2", type = Integer.class) }) // DATAJPA-1579
})

Correctly call a stored procedure using Spring Data JPA

2 Answers2

Linked