ref cursor

Replies to This Discussion

Permalink Reply by Eddie Awad on May 11, 2008 at 3:30pm

I suggest you search/read the documentation to find your answer. Here is a Virtual Book about ref cursors.

Permalink Reply by Ram Kumar on July 24, 2008 at 1:52am

As Eddie mentioned there are lots of documents to look around this. Anyway here are some points that can give you a basic idea.
REF CURSOR is dynamic cursor which points the data's memory location and can be open for more then one query.
There are two type of ref cursor:-
(1) WEEK REF CURSOR:- ref cursor which not have return clause in definition.
(2) STRONG REF CURSOR :- ref cursor which use return clause in definition.

Permalink Reply by Arun Kumar on October 23, 2008 at 10:04pm

Hi
Basically Ref Cursor is a data type. the difference between cursor and the ref cursor is that you can pass a cursor result set to the other programs as (Procedure , functions etc.) with the ref cursor. other wise you have to pass so many parameters to that sub program. Tip: when ever you use the ref cursor use it with %rowtype.

If you need more clarification just reply.

Arun

Permalink Reply by Rajesh B on September 15, 2012 at 6:40pm

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.

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.

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.

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.

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.