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.

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s