Spring Data JPA app calling Oracle package procedure rises PLS-00201 error-CodePudding

I'm trying to make a simple Spring Boot application (using STS 4) that returns some data from an Oracle 19c database. My application uses an annotation-based configuration and accesses only the schema defined in the application.properties file. All works fine until I use simple @Entity classes, but now I'm trying to call a simple procedure defined in a package. The procedure is public, the package has a synonym (even if the schema is always the same) and all the grants:

create or replace package body let_mock is

  function sumfun(p_a In Integer, p_b In Integer) return Integer Is
  Begin
    Return p_a   p_b;
  End;

  Procedure sumproc(p_a In Integer, p_b In Integer, po_res Out Integer) Is
  Begin
    po_res := p_a   p_b;
  End;

end let_mock;

to keep things as simple as possible, I defined the stored procedure call in an Entity class that maps a table

@Entity
@NamedStoredProcedureQuery(name = "SumProc", procedureName = "let_mock.sumproc", 
parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_a", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_b", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "po_res", type = Integer.class) })


@Table(name = TestataLetture.TABLE_NAME)
public class TestataLetture {
    public static final String TABLE_NAME= "LET_TESTATE_LETTURE";
    @Id
    @Column(name = "tele_testata_lettura_id")
    private String lettura_id;

then I mapped the procedure in the Repository interface

public interface RepoLetture extends JpaRepository<TestataLetture, String> {
    @Procedure(name = "SumProc")
    public int getSum(@Param("p_a") Integer a, @Param("p_b") Integer b);
}

but when I call getSum() it always ends up with this error

Hibernate: 
    {call let_mock.sumproc(?,?,?)}
2022-06-14 23:16:06.395  WARN 28180 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 6550, SQLState: 65000
2022-06-14 23:16:06.396 ERROR 28180 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-06550: row 1, column 7:
PLS-00201: identifier 'LET_MOCK.SUMPROC' must be declared
ORA-06550: row 1, column 7:
PL/SQL: Statement ignored

I've tried many changes (adding schema to names, changing parameters name and type and so on) with no results, it seems to me that Hibernate cannot resolve the procedure name if it belongs to a package. Same error for the package function sumfun(). Maybe it works if I wrap it in a dummy "from dual" query, but I don't think it's the right way of doing things.

Any help will be appreciated

EDIT: I've changed

public int getSum(@Param("p_a") Integer a, @Param("p_b") Integer b);

public int getSum(@Param("p_a") int a, @Param("p_b") int b);

with no results

CodePudding user response：

try

@Entity
    @Table(name="CUST", schema="RECORDS")

or in Oracle maybe a synonym would work

CodePudding user response：

having changed the connection properties from

spring.datasource.url=jdbc:oracle:thin:@161.60.160.21/PDBNRTNRG

spring.datasource.url=jdbc:oracle:thin:@161.60.160.81/PDBSVINRG

I solved all problems That is, I was using the wrong db. They are almost identical, almost. Thanks to anyone who could have spent a minute on this post