Ref Cursor:

ref cursor is a data structure which points to an object which in turn points to the memory location.

ex:

create or replace procedure test()

as

begin

type ref_cursor is ref cursor;

open ref_cursor as

select * from table_name;

end;

There are 2 types in this.

1.strong ref cursor:

This has a return type defined.

2. weak ref cursor.

this doesnt have a return type

normal cursor:

Nothing but the named memory location.

it has 2 types

1. explicit cursor

Need to be defined  whenever required.

2.Implicit cursor

need not defined and used by oracle implicitly in DML operation.

Generally ref cursor used for passing cursor parameter and also dynamically building the query.

Normal cursor are static cursors and also we can't able to pass like paramater

Hi Prathima,

Could you please explain in detail the difference with EXAMPLES ?

Has Refcursor anything do with formatting the queries in SQL*Plus ?

Why will i use REFCURSOR instead of CURSOR(Ordinary) ????Is it only for MEMORY AREA ???

Thanks,

Sourav

hi,

the normal cursor is a static cursor i.e we in the normal cursor the query is only assigned at the design time and it can't be change at the run time.

for e.g

create or replace procedure sp_demo_cursor (squery varchar2)is

cursor c1 is squery;

begin

for v1 in c1 loop

---

--

end loop;

end;

assume that there is a requirement that we need to send the query as the parameter of the procedure, in the above case we can't do that. it will give an error.

but the same can be achieved by using the ref cursor.

in other words we can say that ref cursor supports the dynamic change in the cursor.

it also help in memory allocation

hope that above example is helpful and have cleared your doubts.

thanks.

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

Example :-

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
  procedure PrintEmployeeDetails is
  begin
    loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
  end;
begin
  for i in (select deptno,dname from dept)
  loop
    open c_emp for select ename,sal from emp where deptno = i.deptno;
    dbms_output.put_line(i.dname);
    dbms_output.put_line('--------------');
    PrintEmployeeDetails;
    close c_emp; 
  end loop;
end;

In the above program, the sub-routine is named "PrintEmployeeDetails."  You can observe that I am executing (or calling) the sub-routine from within the loop as follows:

  for i in (select deptno,dname from dept)
  loop
      .
      .
      PrintEmployeeDetails;
      .
      .
  end loop;

Another difference is as below.

When we need of getting a resultset from applications like java,.net we used to call procedure which inturn will return ref cursor as out  parameter with resultset.