Oracle Ref cursors

Oracle cursors,Types of cursors

1. REF CURSORS AND CURSOR VARIABLES
1This is unconstrained cursor which will return different types depends
upon the user input.
2Ref cursors can not be closed implicitly.
3Ref cursor with return type is called strong cursor.
4Ref cursor with out return type is called weak cursor.
5You can declare ref cursor type in package spec as well as body.
6You can declare ref cursor types in local subprograms or anonymous
blocks.
7Cursor variables can be assigned from one to another.
8You can declare a cursor variable in one scope and assign another
cursor variable with different scope, then you can use the cursor
variable even though the assigned cursor variable goes out of scope.
9Cursor variables can be passed as a parameters to the subprograms.
10Cursor variables modes are in or out or in out.
11Cursor variables can not be declared in package spec and package
body (excluding subprograms).
12You can not user remote procedure calls to pass cursor variables from
one server to another.
13Cursor variables can not use for update clause.
14You can not assign nulls to cursor variables.
15You can not compare cursor variables for equality, inequality and
nullity.
Ex:
CREATE OR REPLACE PROCEDURE REF_CURSOR(TABLE_NAME IN VARCHAR) IS
type t is ref cursor;
c t;
v_dept dept%rowtype;
type r is record(ename emp.ename%type,job emp.job%type,sal
emp.sal%type);
v_emp r;
v_stud student.name%type;
BEGIN
if table_name = 'DEPT' then
http://ebiztechncis.blogspot.com

2. open c for select * from dept;
elsif table_name = 'EMP' then
open c for select ename,job,sal from emp;
elsif table_name = 'STUDENT' then
open c for select name from student;
end if;
loop
if table_name = 'DEPT' then
fetch c into v_dept;
exit when c%notfound;
dbms_output.put_line('Deptno = ' || v_dept.deptno || ' Dname
= ' ||
v_dept.dname || ' Loc = ' ||
v_dept.loc);
elsif table_name = 'EMP' then
fetch c into v_emp;
exit when c%notfound;
dbms_output.put_line('Ename = ' || v_emp.ename || ' Job = '
|| v_emp.job
|| ' Sal = ' || v_emp.sal);
elsif table_name = 'STUDENT' then
fetch c into v_stud;
exit when c%notfound;
dbms_output.put_line('Name = ' || v_stud);
end if;
end loop;
close c;
END;
Output:
SQL> exec ref_cursor('DEPT')
Deptno = 10 Dname = ACCOUNTING Loc = NEW YORK
Deptno = 20 Dname = RESEARCH Loc = DALLAS
Deptno = 30 Dname = SALES Loc = CHICAGO
Deptno = 40 Dname = OPERATIONS Loc = BOSTON
http://ebiztechncis.blogspot.com

3. SQL> exec ref_cursor('EMP')
Ename = SMITH Job = CLERK Sal = 800
Ename = ALLEN Job = SALESMAN Sal = 1600
Ename = WARD Job = SALESMAN Sal = 1250
Ename = JONES Job = MANAGER Sal = 2975
Ename = MARTIN Job = SALESMAN Sal = 1250
Ename = BLAKE Job = MANAGER Sal = 2850
Ename = CLARK Job = MANAGER Sal = 2450
Ename = SCOTT Job = ANALYST Sal = 3000
Ename = KING Job = PRESIDENT Sal = 5000
Ename = TURNER Job = SALESMAN Sal = 1500
Ename = ADAMS Job = CLERK Sal = 1100
Ename = JAMES Job = CLERK Sal = 950
Ename = FORD Job = ANALYST Sal = 3000
Ename = MILLER Job = CLERK Sal = 1300
SQL> exec ref_cursor('STUDENT')
Name = saketh
Name = srinu
Name = satish
Name = sudha
http://ebiztechncis.blogspot.com