0

I have a stored procedure in PostgreSQL that returns a refcursor and I want to call it using Spring Jdbc SimpleJdbcCall.

The procedure code is given below:

CREATE OR REPLACE FUNCTION show_emps() RETURNS refcursor AS $$
DECLARE
  ref refcursor;                                     -- Declare a cursor variable
BEGIN
  OPEN ref FOR SELECT emp_id, first_name FROM emp;   -- Open a cursor
  RETURN ref;                                                      
END;
$$ LANGUAGE plpgsql;

Can anyone help me with the code to call this procedure and fetch its data using SimpleJdbcCall class. I am using the following code for the call, but its not working:

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate);
jdbcCall.withProcedureName("show_emps");

Map<String, Object> result = jdbcCall.execute();
Vishuu
  • 1
  • 5
  • Why a refcursor at all? Use `returns table (...)` then you can simply run `select * from show_emps()` –  Jan 09 '20 at 09:32
  • Actually, its a given condition and there is already a stored procedure defined in the database that resembles this definition, that's why I want the answer specifically for refcursor. – Vishuu Jan 09 '20 at 09:43

0 Answers0