CODEWITHSUNDEEP
javaoracle-databasestored-proceduresspring-data-jpa
valepu
valepu

Reputation: 3315

Correctly call a stored procedure using Spring Data JPA

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;

Upvotes: 2

Views: 16754

Answers (2)

valepu
valepu

Reputation: 3315

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

Upvotes: 1

Kevin Valdez
Kevin Valdez

Reputation: 389

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

Upvotes: 1

Related Questions