KiranKW

 Profile Answers by KiranKW 

• May 2nd, 2007

 

Hi,

   Please find the explanation below....

CURSOR

A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query.

  A cursor can be used in realtime when u need to fetch some records and do some kind of an operation on each of these records .. which cannot be done with a single DML...

REFERENCE CURSOR

A cursor variable is a data structure that points to a cursor object, which in turn points to the cursor's result set. You can use cursor variables to more easily retrieve rows in a result set from client and server programs. You can also use cursor variables to hide minor variations in queries.

The syntax for a REF_CURSOR type is:

TYPE ref_cursor_name IS REF CURSOR    [RETURN record_type];

If you do not include a RETURN clause, then you are declaring a weak REF CURSOR. Cursor variables declared from weak REF CURSORs can be associated with any query at runtime. A REF CURSOR declaration with a RETURN clause defines a "strong" REF CURSOR. A cursor variable based on a strong REF CURSOR can be associated with queries whose result sets match the number and datatype of the record structure after the RETURN at runtime.

To use cursor variables, you must first create a REF_CURSOR type, then declare a cursor variable based on that type.

   Reference cursors on the other hand are used in realtime for dynamic purposes ... like when its required that you need to do an operation not on a single table but multiple tables and u cannot use cursor for each table to do the operation ... u can used dynamic or reference cursor to do this operation.

 

  Hope this cleared your doubt.

msenthil19

 Profile Answers by msenthil19 

• Mar 18th, 2009

 

A CURSOR normally selects the records given by the query in a separate buffer
area and these fetched records will be referenced or make used in the program by
looping structures like FOR LOOP etc.

A REF CURSOR is used as a reference type. for Ex:- consider a Procedure that
returns some OUT values once called. You can create a variable as a REF CURSOR
type variable that matches the Procedures output i.e., the REF CURSOR (variable)
will be in the form that it can hold the OUT values of the Procedure and the
result can be returned to the called environment using this REF CURSOR type
variable.

sunil

• Jul 26th, 2011

 

CURSOR

A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query.

A cursor can be used in realtime when u need to fetch some records and do some kind of an operation on each of these records .. which cannot be done with a single DML...

REFERENCE CURSOR

A cursor variable is a data structure that points to a cursor object, which in turn points to the cursor's result set. You can use cursor variables to more easily retrieve rows in a result set from client and server programs. You can also use cursor variables to hide minor variations in queries.

Art11

 Profile Answers by Art11 

• Sep 27th, 2011

 

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value. It is not a cursor, but a variable that points to a cursor.
It is not limited to one query.
The purpose of ref cursors is to be able to share cursors and result sets between the client and the Oracle server or between different subroutines. A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time.
For example you might open a cursor in an Oracle Forms client and then continue working with the cursor on the server or you might open a cursor in say a Java program and then continue working with it in a PL/SQL stored procedure.

You can find so many examples online... Here's one of them:
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php