What is the difference between REF CURSOR and Normal Cursor?

The cursor concept is same between regular cursor(PLSQL cursor) and ref cursor. But normal cursor, we can declare and define the cursor in the declaration part. In the declaration itself, the select statement is tied up with the cursor. So the cursor structure is known in the compile time. It is static in definition.

In ref cursor, we just declare the variable as SYS_REFCURSOR data type. We are not tying with any select statement in the declaration. But inside the procedure, we can tie up the same ref cursor variable with any number of select statement. It is dynamic and dynamically opened in the procedure, based on the logic or condition.

Let us demonstrate this... I have stored procedure and i am passing input 1 or 2. If i pass 1, then i wanted to display emp table records. If i pass input 2, then i wanted to display dept table. I am demonstrating this in normal cursor and ref cursor.

Here is the way to accomplish this in Normal cursor.

create or replace procedure Test_refcursor(p_choice NUMBER) is
cursor c1 is select * from emp;
cursor c2 is select * from dept;
begin
if p_choice = 1 then
for i in c1 loop
dbms_output.put_line(i.ename);
end loop;
elsif p_choice = 2 then
for i in c2 loop
dbms_output.put_line(i.dname);
end loop;
end if;
END;
/

Here is the way to accomplish this in ref cursor.

Create or replace procedure test_refcursor(p_choice number) is
c1 sys_refcursor;
v_ename emp.ename%type;
v_dname dept.dname%type;
procedure gen_cur(chc IN number,b IN OUT sys_refcursor) is
str varchar2(1000);
begin
if chc = 1 then
str:= 'select ename from emp';
elsif chc = 2 then
str:= 'select dname from dept';
end if;
open b for str;
end;
begin
gen_cur(p_choice,c1);
if p_choice = 1 then
loop
fetch c1 into v_ename;
exit when c1%notfound;
dbms_output.put_line(v_ename);
end loop;
elsif p_choice = 2 then
loop
fetch c1 into v_dname;
exit when c1%notfound;
dbms_output.put_line(v_dname);
end loop;
end if;
end;
/

2. Ref cursor output can be returned to client(java, .Net, VB, reporting tool etc) application. But normal cursor(PLSQL cursor) output can not be returned to client application.

3. Normal cursor can be global. For example, we can declare the normal cursor in the package specification. It can be used in all procedure/functions in the same package as well as outside the package. But ref cursor can not be declared outside of the procedure.

4. Normal cursor can not be passed from one subroutine to another subroutine. But ref cursor can be passed from one subroutine to another subroutine.