Monday, May 17, 2010

Oracle - Using Ref Cursors

Ref Cursors are variables which can point to any cursor dynamically, or can be passed as parameter in some procedure, function or java program, etc.

The PROC below takes a REF CURSOR as a parameter and iterates to print the data contained in the cursor.

create or replace procedure emp_proc_ref_cur (c_emp SYS_REFCURSOR ) is
type rec_emp is record
name varchar2(20),
sal number(6)
er rec_emp;
open c_emp;
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line( || ' - ' || er.sal);
end loop;

The PROC below creates a REF CURSOR and OPENS it on EMP table, and passes that REF CURSOR as parameter to the PROC above.

create or replace procedure emp_rec_pro is
type emp_ref is REF CURSOR;
emp_cur emp_ref;
open emp_cur for select ename name, sal from emp where sal>250;
emp_proc_ref_cur (emp_cur);

No comments:

Post a Comment

Amazon Best Sellors

Weblogic - Server Creation - Issues

Datasource creation using python script.  Python Script was failing after creating Datasource. From UI Admin Console, if I try to activat...