oracle-databasestored-proceduresspring-data-jpa

How to write an Oracle procedure with a select statement for Spring Data JPA method

I've written a @Procedure method in a Jpa Repository for the procedure mentioned below

CREATE OR REPLACE PROCEDURE SELECT_USERS_BY_ID(user_id_in IN varchar2,cur OUT SYS_REFCURSOR) AS 
BEGIN
  OPEN cur FOR SELECT * FROM users WHERE user_id = user_id_in;
END SELECT_USERS_BY_ID;

oracle sql developer version:23+ spring version:3.3+ java:17

Below is the method I've written, and why is writing procedures vary from one DB to another

@Procedure(procedureName = "SELECT_USERS_BY_ID")
    User fetchByUserId(@Param("user_id_in")String userId);

Tried doing by adding this annotation above my user entity class

@NamedStoredProcedureQuery(procedureName = "SELECT_USERS_BY_ID", name = "fetchUserById",
        parameters = {@StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "user_id_in"),
                @StoredProcedureParameter(mode = ParameterMode.OUT, type = User.class, name = "cur")})

still didn't work, or if my procedure itself is wrong please correct, Thanks

Solution

  • The cursor StoredProcedureParameter should be of void.class and you need a resultClasses = User.class as parameter of the NamedStoredProcedureQuery.