prathima

• Oct 21st, 2005

 

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.

Mehtab

• Oct 24th, 2005

 

whats the diffrance between ref cursor and normal cursor in detail

suresh

• Nov 29th, 2005

 

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

Sourav

• Jan 3rd, 2006

 

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

aseemnaithani

 Profile Answers by aseemnaithani Questions by aseemnaithani

• May 17th, 2006

 

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.

Ram Prasad

• Apr 19th, 2007

 

In case of an normal explict cursor, the SQL query has to be defined at the time of declaring the cursor itself.  In case of REF Cursor, the cursor declartion is not associated with any SQL query, it is associated with a query at a later stage this brings in a lot of flexibility as different SQL queries can be associated with the cursor (one at a time, offcourse) programatically.  REF Cursors also provide the feature of passing parameters.  Though there is something dynamic with REF Cursor when compared to a normal explicit cursor, it is not a truly perfect dynamic cursor.  Truly perfect dynamic cursors are the one constructed using DBMS_SQL package.

nambiaruran

• Jun 29th, 2007

 

Ref cursor is a cursor variable that point to any cursor, mainly used for returning the cursor output.

anil.lohan

• Jul 4th, 2007

 

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;

ilangovan

• Aug 31st, 2007

 

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.

SATYANARAYAN KANUNGO

 Profile Answers by SATYANARAYAN KANUNGO 

• Jun 8th, 2008

 

 A REF CURSOR is basically a data type.

nandk.sharma

 Profile Answers by nandk.sharma 

• Jun 9th, 2008

 

REF CURSOR - This cursor could be used for processing more than one SELECT query.
                      

NORMAL COUSIR - This cursor could be used for processing  only one SELECT query.

Amit Yadav

• May 23rd, 2012

 

Normal Cursor is static and can be associated with only single query where as ref cursor is dynamic and can be associated with multiple queries.

Ref cursor is basically uesd to pass result set to procedure or function which can be used by third party application and can be strong type or weak type.

Ashok Kumar Lenka

• Dec 6th, 2012

 

SELECT TO_CHAR (ADD_MONTHS (SYSDATE, -ROWNUM), dd-Mon-yyyy)
FROM user_objects
WHERE ROWNUM < 25
ORDER BY ROWNUM DESC

Ramesh Guled

• Dec 9th, 2012

 

In case of an normal explict cursor, the SQL query has to be defined at the time of declaring the cursor itself. In case of REF Cursor, the cursor declartion is not associated with any SQL query, it is associated with a query at a later stage this brings in a lot of flexibility as different SQL queries can be associated with the cursor (one at a time, offcourse) programatically. REF Cursors also provide the feature of passing parameters. Though there is something dynamic with REF Cursor when compared to a normal explicit cursor, it is not a truly perfect dynamic cursor. Truly perfect dynamic cursors are the one constructed using DBMS_SQL package.

Ashish Kaushal

• Dec 11th, 2012

 

Ref cursor is nothing but a work around for limitation of PL/SQL i.e. rigid blocks. Since all the declarations must be made in declaration section. If you know your SELECT statement you can use I use regular cursor. If your SELECT is dynamic e.g. it changes with some variable value (v_manage=1 then this else that) then its a problem because you can check the variable value in Execution section only. The work around is Declare ref cursor and assign it SQL SELECT in execution section.

Ramesh.RV

• Feb 26th, 2013

 

The main reason is refcursor is a address it give the address of the location instead of item. It hold the different type of structures. Normal cursor holds a one structure of the table.

rammohan

• Apr 8th, 2013

 

REF cursor is a dynamic cursor where as normal cursor is static cursor,In dynamic cursor single segment are process multiple SELECT statements dynamically at run time, where as in normal cursor we process only one select statement

Annaiah

• Jun 27th, 2014

 

Ref cursor is typically used to return a record set or cursor from a stored procedure.ref cursor is basically data type
ref cursor can be changed dynamically at run time normal cursor is static cursor in which the query is assigned at design time and cant be changed at run time

normal cursor is static cursor ref cursor is a dynamic cursor
ref cursor refers different work area in memory where normal cursor always refers one work area in memory
ref cursor can be called from one procedure to another procedure normal cursor cant be called from one procedure to another procedure

niranjan

• Nov 25th, 2015

 

Can you explain with one example? Rammohan..

Soumya

• Dec 29th, 2015

 

Ref cursor allows you to open a context are without any queries. Where as the normal cursor since the time of declaration to the time the data is committed holds the query in the context area and releases the context area/memory only after the data is committed. So if you have a 100 select or DML queries in the PL/SQL block instead of declaring a 100 cursor to implement those 100 statements you can declare a refcursor and use it to execute all your 100 statements.

Rupesh

• Aug 29th, 2017

 

Normal cursor is associated with one select statement but ref cursor is associated with more than one select statement.ref cursor is dynamics cursor.

Devanath Behera

• Oct 26th, 2017

 

The basic difference between Normal Cursor and Ref Cursor is as follows:
1. A Normal Cursor is a simple cursor which act as a static one where as Ref Cursor is a Dynamic Cursor which acts dynamically at run time.
2. A Normal Cursor behaves likes a constant whereas a Ref Cursor behaves like a variable.
3. A Normal Cursor can not be pass as a parameter where as Ref Cursor can be pass as a parameter to a procedure.
4. One one SELECT statement can be process by using a normal cursor where as we can more than one SELECT statement.
5. We need not to create a TYPE for processing of data in Normal Cursor whereas we can create a TYPE for the ref cursor if we are not using sys_ref cursor.