Ref Cursors Examples

REF CURSOR is a data type in the Oracle. To read more about REF CURSOR

I have created a package to explain few different ref cursor examples.
It covers most of the typical and simple examples.

create or replace package pkg_refcur 
is
— Declaring a Strong Ref Cursor Type
TYPE ref_strong_emptyp IS REF CURSOR RETURN emp%ROWTYPE;

— Declaring a Weak Ref Cursor Type
TYPE ref_weak_typ IS REF CURSOR;

–Package Signatures
procedure p_get_employees(pi_deptno   in  integer,
po_results  out ref_strong_emptyp);

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ);

procedure p_get_deptemp(pi_deptno  in  integer,
po_results out sys_refcursor);

procedure p_getdeptemp(pi_deptno  in  integer,
pi_type    in  varchar2,
po_results out sys_refcursor);

procedure p_dynamic(pi_tablename  in   integer,
po_results    out  sys_refcursor);

End pkg_refcur;
/

create or replace package body pkg_refcur
is
— 1)
— Using Strong ref cursor to return employees in a department
procedure p_get_employees(pi_deptno  in  integer,
po_results  out ref_strong_emptyp)
is
Begin
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_employees;

— 2)
— Using weak ref cursor to return employees and department details
procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ)
is
Begin
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_dept_emp;

— 3)
— Using system ref cursor to return employees and department details
procedure p_get_deptemp(pi_deptno  in  integer,
po_results out sys_refcursor)
is
Begin
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_deptemp;

— 4)
— Associating multiple query
procedure p_getdeptemp(pi_deptno  in  integer,
pi_type    in  varchar2,
po_results out sys_refcursor)
is
Begin
if upper(pi_type) = ‘DEPT’ then
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
elsif upper(pi_type) = ‘EMP’ then
Open po_results for
select * from dept e
where e.deptno = pi_deptno;
elsif upper(pi_type) = ‘BOTH’ then
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
end if;
Exception
when others then
raise;
End p_getdeptemp;

— 5)
— Associating multiple query with dynamic query
procedure p_dynamic(pi_tablename  in  integer,
po_results    out sys_refcursor)
is
Begin
if upper(pi_tablename) in (‘EMP’,’DEPT’) then — for security
Open po_results for
‘select * from ‘ || pi_tablename ;
end if;
Exception
when others then
raise;
End p_dynamic;

End pkg_refcur;
/

Conclusion :- Ref Cursors are very ideal for interchanging data between two different application involving Oracle as a database, like Java and Oracle, Dot net and oracle, Oracle forms and Oracle etc.