CODEWITHSUNDEEP
sqlspringspring-bootstored-proceduresspring-data-jpa
user5084649
user5084649

Reputation:

Spring Data Jpa call to Stored Procedure

I am trying to create a method that call Stored Procedure to do a very easy insert and a select,

I created a stored procedure on an Oracle database as below:

CREATE OR REPLACE PROCEDURE schema.insertNewTable(p_test IN VARCHAR2, p_out OUT VARCHAR2)
IS
BEGIN
INSERT INTO NEWTABLE("TEST") VALUES(p_test);
SELECT '1' INTO p_out FROM DUAL;

COMMIT;

END;

Then on an Entity:

@Procedure(procedureName = "insertNewTable", outputParameterName="p_out")
String insertNewTable(@Param("p_test") String p_test);

but everytime i call this method i get this:

2018-06-23 20:04:43,047|ORA-06550: row 1, colonna 7:
PLS-00306: wrong number or types of arguments in call to 'INSERTNEWTABLE'
ORA-06550: riga 1, column 7:
PL/SQL: Statement ignored

2018-06-23 20:04:43,100|org.springframework.dao.InvalidDataAccessResourceUsageException: Error calling CallableStatement.getMoreResults; SQL [insertNewTable]; nested exception is org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:503)

After a couple of tries (and exceptions), I modified the Entity and the call to avoid "OUT" types and, in this way, I have no errors:

CREATE OR REPLACE PROCEDURE schema.insertNewTable(p_test IN VARCHAR2)
IS
BEGIN
INSERT INTO NEWTABLE("TEST") VALUES(p_test);

COMMIT;

END;

Then on an Entity:

@Procedure(procedureName = "insertNewTable")
void insertNewTable(@Param("p_test") String p_test);

How can i fix this to get a return value?

Upvotes: 2

Views: 8465

Answers (2)

Soodabeh Neirizi
Soodabeh Neirizi

Reputation: 85

with Spring DATA

in your repository:

1) calling with native SQL :

@Query(value = "select Store-procedure-name(:param) from dual", nativeQuery = true) BigDecimal method-name(@Param("param") Long param);

2)

@Procedure(procedureName = "store-procedure-name", outputParameterName = "output-param-of-store-procedure")

return-type-of-store-procedure  method-name(input-params-of-store-procedure);

Upvotes: 1

Indrakumara
Indrakumara

Reputation: 1645

you can use NamedStoredProcedureQuery

@NamedStoredProcedureQuery(
    name = "insertNewTable", 
    procedureName = "insertNewTable", 
    parameters = { 
        @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name 
= "p_test"), 
        @StoredProcedureParameter(mode = ParameterMode.OUT, type = String.class, name 
= "p_out"), 
      )
    }
)

method call:

StoredProcedureQuery query = 
this.em.createNamedStoredProcedureQuery("insertNewTable");
query.setParameter("p_test", ""TEST"");
query.execute();
String out =  query.getOutputParameterValue("p_out");

Upvotes: 0

Related Questions