@NamedStoredProcedureQuery is not working for the simple procedure call in JPA Java

user_16568836 0
Viewed 122

I am getting an error : Attempt to set a parameter name that does not occur in the SQL: i_reorg_id . It does not make sense to me as there is i_reorg_id in the SQL.

Procedure is:

create or replace PROCEDURE PRINTREORGID(i_reorg_id IN VARCHAR2, o_reorg_id OUT VARCHAR2)
AS BEGIN
  SELECT reorg_id
  INTO o_reorg_id
  FROM reorg_automation_workflowinput
  WHERE reorg_id = i_reorg_id;
END;

Entity is:

@Entity
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(name = "fetchProcedure", procedureName = "PRINTREORGID", parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "i_reorg_id"),
                @StoredProcedureParameter(type = String.class, mode = ParameterMode.OUT, name = "o_reorg_id"), 
                }) 
        })

And Repository is:

    @Procedure(name = "fetchProcedure", procedureName="PRINTREORGID")
    String reorgAutomationWorkFlow(@Param("i_reorg_id") String i_reorg_id);
spring-boot procedure jpa stored-procedures java
1 Answers

You can call the procedure another way(This is Best Practice):-

Procedure:-

procedure getEmployeeById(
        id_in IN EMPLOYEE.ID%type,
        e_disp OUT SYS_REFCURSOR
    ) IS
        hasEmployee number;
    BEGIN
        hasEmployee := 0;
        SELECT count(*) into hasEmployee from EMPLOYEE where ID = id_in;
        IF hasEmployee <> 0 THEN --here <> means !=
            OPEN e_disp FOR SELECT * FROM EMPLOYEE WHERE ID = id_in;
        ELSE
            --return empty SYS_REFCURSOR couse 1=2 not equal always
            OPEN e_disp FOR SELECT * FROM EMPLOYEE WHERE 1=2;
        END IF;
    END getEmployeeById;

Call From Java:- Click here

user_132565030
Related