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.
