dBEAVER 7.3.3 how to execute oracle stored procedure

Question

please can you help me to run/execute oracle procedure in dBEAVER? Here is the block where you can see input parameters enter image description here

Answer 1

Procedure with OUT cursor

I've found no way to execute procedure with cursor output parameter and show it in the result grid. So I see no other way than binding the cursor in the anonymous block (then do with the cursor what you need).

CREATE PROCEDURE test_proc(
  datemin DATE,
  datemax DATE,
  RES OUT sys_refcursor
)
AS
BEGIN
 OPEN res
 FOR SELECT datemin + LEVEL
       FROM dual
    CONNECT BY LEVEL < datemax - datemin;
END;

DECLARE
  l_cur sys_refcursor;
  l_datemin DATE;
  l_datemax DATE;
  l_date date;
BEGIN
  l_datemin := to_date(:dmin, 'YYYY-MM-DD');
  l_datemax := to_date(:dmax, 'YYYY-MM-DD');
  
  -- execute the procedure
  test_proc(l_datemin, l_datemax, l_cur);

  -- do something with the cursor, here just print to output
  loop
    fetch l_cur into l_date;
    exit when l_cur%notfound;
    dbms_output.put_line(to_char(l_date, 'YYYY-MM-DD'));
  end loop;
END;

Function returning cursor

This case looks better, the returned cursor can be displayed in query grid and it's value inspected (You'll get probably asked about settings that prevent closing the cursor upon executing the query, choose the option that keeps the cursor upened).

create or replace function test_fun(
  datemin DATE,
  datemax DATE
) return sys_refcursor
AS
  l_res sys_refcursor;
BEGIN
 OPEN l_res
 FOR SELECT datemin + LEVEL
       FROM dual
    CONNECT BY LEVEL < datemax - datemin;
 RETURN l_res;
END;

SELECT
  1 a,
  'xyz' b,
  test_fun(to_date(:dmin, 'YYYY-MM-DD'), to_date(:dmax, 'YYYY-MM-DD')) c
FROM dual;