CODEWITHSUNDEEP
spring-bootplsqlspring-data-jpa
jesse
jesse

Reputation: 71

Error while calling stored procedure from Spring Data

I tried several different ways to access to my stored procedure via my spring boot application but I have always Errors like :

InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

or

Caused by: java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'MY_STORED_PROCEDURE_NAME' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

I tried :

 StoredProcedureQuery procedureQuery = entityManager.createStoredProcedureQuery("MY_STORED_PROCEDURE_NAME");
        procedureQuery.registerStoredProcedureParameter("paramOut", Object.class, ParameterMode.OUT);
        procedureQuery.execute();
        procedureQuery.getOutputParameterValue("paramOut");

@Transactional
@Procedure(procedureName= "MY_STORED_PROCEDURE_NAME" )
Object getNextNumber();

@Query(value = "{call MY_STORED_PROCEDURE_NAME()}", nativeQuery = true)
@Transactional
Object getNextNumber();

but always it seems he can't reach my stored procedure in my DB : I added grand grant execute on ... to ... but that doesn't solve the problem

any idea, please

Upvotes: 1

Views: 2546

Answers (1)

jesse
jesse

Reputation: 71

Thank you for your response So I resolved the bug related to call the stored procedure by preceding procedure's name with its owner name :

my_schema.my_stored_procedure_name

and use

EXECUTE IMMEDIATE 'UPDATE ...

inside the stored procedure

java code

`StoredProcedureQuery procedureQuery entityManager.createStoredProcedureQuery("my_schema.MY_STORED_PROCEDURE_NAME");

procedureQuery.registerStoredProcedureParameter("in1", Long.class, 
ParameterMode.IN);

procedureQuery.registerStoredProcedureParameter("in2", String.class, 
ParameterMode.IN);
procedureQuery.registerStoredProcedureParameter("out1", Long.class, 
ParameterMode.OUT);

 procedureQuery.registerStoredProcedureParameter("out2", String.class, 
 ParameterMode.OUT);

        procedureQuery.setParameter("in1", val1);
        procedureQuery.setParameter("in2", val2);
        procedureQuery.execute();
        procedureQuery.getOutputParameterValue("out1");
        procedureQuery.getOutputParameterValue("out2");`

Upvotes: 1

Related Questions