SQLDeveloper execute procedure with parameters-CodePudding

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?

CodePudding user response：

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):

CodePudding user response：

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.