[FIXED] Calling database function JPA repository

Issue

i have a question.

I want to call a database function from my JPARepository in spring boot... My function is the next:

CREATE function sf_getval(seqname varchar2) return NUMBER IS ret_val number :=0; 
begin
 INSERT INTO schema.table(IDENT ,NAME) VALUES (12321,'Name');
  return ret_val;
  END sf_getval;

That is not doing anything, i just want a function that insert something in database and return a number, i need this, cant change, is the definition.

Then from JPA i need to consume like this:

@Repository
public interface myRepository extends JpaRepository<Some, SomeId> {

@Query(nativeQuery = true, value = "CALL \"pkgName\".\"sf_getval\"(:name) ")
int sf_getval(@Param("name") String name);

If i do a select pkgname.sf_getval() var from dual; did not work because that violates isolation in the database, is not an option to me. Necesary must be a call command.

I use de repository directly because in my project i've already configure spring.cloud.config and i dont need entityManager or something like that. Is not a solution do a jdbc call.

Thanks, sorry for my english. Regards

Solution

Finally I found the answer:

String call = "{ ? = call FCRLIVE.AP_CH_GET_ACCT_BALANCES(?, ?, ?, ?, ?) }";
CallableStatement cstmt = conn.prepareCall(call);
cstmt.setQueryTimeout(1800);
cstmt.setString(1, inputCode);
cstmt.registerOutParameter(2, Types.NUMBER);
cstmt.executeUpdate();

Is not the exactly code, but getting the connection from the entityManager, in pure JPA is the solution.

Answered By - Emmanuel Acosta