SQLDeveloper execute procedure with parameters

Question

I'm struggling to find a way to execute a procedure that has a SYS_REFCURSOR I found different ways and it appears is easier in sqlplus but I wanted to do it through a query in sqldeveloper

The query takes 3 parameters and has a 4th that is a SYS_REFCURSOR

procedure_example(var1 number,var2 varchar2,var3 varchar2,result out sys_refcursor)

How would I execute something like this in SQLDEVELOPER?

What do you mean by "through a query"? [This approach](https://stackoverflow.com/a/8618084/266304) works in both SQL\*Plus and SQL Developer, but I wouldn't describe it as a query. What have you tried that works in SQL\*Plus but not in SQL Developer? — Alex Poole, Feb 16 '22 at 11:21

score 3 · Accepted Answer · answered Feb 16 '22 at 12:07

As this is a procedure with an OUT parameter, you'll have to use another PL/SQL block which has DECLARE section (so that you'd have "something" to accept what your procedure returns).

declare
  l_rc sys_refcursor;
begin
  procedure_example(var1   => 1,
                    var2   => 2,
                    var3   => 3,
                    result => l_rc);
end;
/

(You'd pass meaningful values to IN parameters, of course.)

Another option is to declare a variable, use it while executing the procedure and print its contents. For example (based on Scott's sample schema):

score 0 · Answer 2 · answered Feb 16 '22 at 15:27

The GUI way...using @littlefoot's code:

create or replace procedure p_test(
 par_deptno in number, 
 par_rc out sys_refcursor)
is
begin
 open par_rc for
  select employee_id,
                 first_name,
                 last_name,
                 job_id,
                 salary
    from employees
    where department_id = par_deptno;
end;

Open in the database (or file), and hit the RUN button.

Set the input parameter, then click 'Ok' - the procedure will run, and you can see your refcursor below.

SQLDeveloper execute procedure with parameters

2 Answers2