Correctly call a stored procedure using Spring Data JPA

java oracle stored-procedures spring-data-jpa

13,057

Solution 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

Solution 2

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
})

13,057

Author by

valepu

Apparently, this user prefers to keep an air of monsters about them.

Updated on June 14, 2022

Comments

valepu almost 2 years

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;