create or replace package demo_pckg
as
type cursor_type is ref cursor;
procedure demo_proc(i_deptno number,o_result out cursor_type);
end;
/
create or replace package body demo_pckg
as
procedure demo_proc(i_deptno number,o_result out cursor_type)
as
begin
open o_result for
select * from employees
where department_id=i_deptno;
end;
end;
/
What should I do in case I want to print that OUT cursor variable ?
The simplest way from SQL*Plus or SQL Developer is with variable and print:
var deptno number
var result refcursor
exec :deptno := 10;
exec demo_pckg.demo_proc(:deptno, :result);
print result
Or:
var result refcursor
declare
deptno emplyees.department_id%type;;
begin
deptno := 10;
demo_pckg.demo_proc(deptno, :result);
end;
/
print result
The result is treated as a bind variable in the procedure call, so it's prefixed with : there, but it's a native variable to SQL*Plus so it doesn't have one for the print call. You can run either in SQL*Plus, or as a script in SQL Developer, which will show the results in the Script Output window.
You can hard-code the deptno value in the procedure call in either case, of course, rather than delcaring a variable to hold that.
If you're calling this from Java or some other client program you can treat your OUT cursor like any other result set.
You can also declare result as a sys_refcursor in your procedure, rather than declaring your own type.
dbms_output.put_line prints to standard output. You need to enable server output to see the results with: set serveroutput on. Turning server output on should be done in your environment like sqlplus, etc.
You will need to select results into a variable with select column1 into var1 from ... so that you can print them later with dbms_output.put_line('my var1: ' || var1);
