Difference between a cursor and reference cursor

Showing Answers 1 - 14 of 14 Answers

abina

  • Aug 8th, 2005
 

A REF CURSOR have a return type and it as 2 type Strongly Typed Cursor and Weakly Typed Cursor  
 
but Cursor doesn't have return type 
 
Ex: 
 
TYPE ref_type_name IS REF CURSOR RETURN return_type; 
 
return_type represents a record in the database 
 
DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE; 
 
 
 

abina

  • Aug 8th, 2005
 

Cursor doesn't have a return type but 
A Reference Cursor have a return type and it as 2 type one is  
Strongly Typed Cursor and Weakly Typed Cursor. 

  Was this answer useful?  Yes

Taral Desai

  • Sep 2nd, 2005
 

Another difference is REF curson can be assigned dynamically while Normal cursor once defined you cann't change it

  Was this answer useful?  Yes

keerthi

  • Sep 22nd, 2005
 

ref cursor can be associated with many no. of sql statements where cursor can be associated only with one sql statement.

ref cursor is dynamic,cursor is static.

ref cursor points to a location.

  Was this answer useful?  Yes

Ramesh

  • Sep 28th, 2005
 

Reference cursors have 2 types.

1 is strong cursors and 2 one is week-cursor

in stron cursor we given return type. in week cursor no return type

Regards

Ramesh G

  Was this answer useful?  Yes

jitu

  • Oct 17th, 2005
 

A REF CURSOR have a return type and it as 2 type Strongly Typed Cursor and Weakly Typed Cursor  
 
but Cursor doesn't have return type 
 
Ex: 
 
TYPE ref_type_name IS REF CURSOR RETURN return_type; 
 
return_type represents a record in the database 
 
DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE; 
***********************************

Cursor doesn't have a return type but 
A Reference Cursor have a return type and it as 2 type one is  
Strongly Typed Cursor and Weakly Typed Cursor. 

**************************************************

ref cursor can be associated with many no. of sql statements where cursor can be associated only with one sql statement.

ref cursor is dynamic,cursor is static.

ref cursor points to a location.

  Was this answer useful?  Yes

prathima

  • Oct 21st, 2005
 

CURSOR

In cursor there are 2 types explicit and implicit cursor

Explicit cursor

Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration section of the current block or in a package specification. Use OPEN, FETCH, and CLOSE in the execution or exception sections of your programs.

IMPLICIT CURSOR

Whenever a SQL statement is directly in the execution or exception section of a PL/SQL block, you are working with implicit cursors. These statements include INSERT, UPDATE, DELETE, and SELECT INTO statements. Unlike explicit cursors, implicit cursors do not need to be declared, OPENed, FETCHed, or CLOSEd.

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.

The following example shows the use of both weak and strong REF CURSORs:

DECLARE   -- Create a cursor type based on the companies         table.   TYPE company_curtype IS REF CURSOR       RETURN companies%ROWTYPE;   -- Create the variable based on the REF CURSOR.   company_cur company_curtype;   -- And now the weak, general approach.   TYPE any_curtype IS REF CURSOR;   generic_curvar any_curtype;

The syntax to OPEN a cursor variable is:

OPEN cursor_name FOR select_statement;

Gupta

  • Aug 7th, 2006
 

Hi

Cursor (explicit cursor) are static cursors which can be associated with onlyone SQl statement at the same timeand this statement is known when block is compiled. A Cursor Variable,  on the other hand, can be associated with different queries at runtime.

Static Cursor are analogus to PL/SQL constants because they can only be associated with one runtime query, whereas reference cursor are analogus to PL/SQL variables, which can hold different values at runtime.

Reference Cursor  can have return type.

Beacause of reference type,  no storage is allocated for it when it is declared. Before it can be used, it needs to point to a valid area of memory, which can be created either by allocating it to the client-side program or on the server by PL/SQL engine.

  Was this answer useful?  Yes

Pawan Ahuja

  • Dec 3rd, 2006
 

Ref cursor can contain  multiple query in single variable. where cursor can be associated only with one sql query

Ref cursor is dynamic,cursor is static.

Regards

Pawan Ahuja

  Was this answer useful?  Yes

JITENDRA

  • Jan 16th, 2007
 

CURSOR IS A STATIC TYPE. BUT  REF CURSOR IS DYNAMIC.

  Was this answer useful?  Yes


Cursor is a private Sql area which is used to execute sql statements and store Processing information, Where as Refcursor is a data structure which points to an object which inturn points to memmory Location

The advantage Refcursor over Normal Cursor is

1.We can pass cursor data as parameter to the procedure or function.
2.without closing a cursor,we can open a cursor again.

  Was this answer useful?  Yes

karthick

  • Sep 17th, 2011
 

in cursor we can use same sql statements multiple times.
ref cursor we can use multiple sql statements.

  Was this answer useful?  Yes

pradeep

  • Jan 8th, 2012
 

Normal cursors do have a return type.

ex:

Code
  1. CREATE package pkg_test IS

  2. cursor c1 RETURN emp%rowtype;

  3. end pkg_test;

  4.  

  5. CREATE package body pkg_test IS

  6. cursor c1 RETURN emp%rowtype IS SELECT * FROM emp WHERE empno = 10;

  7. begin

  8. FOR emp_cv IN c1 loop

  9. ..

  10. end pkg_test;

  11.  

  Was this answer useful?  Yes

PRADEEP

  • Oct 16th, 2012
 

Ref cursor is having return type where as cursor does not have .
syntax of ref cursor:- type ref_type_name is ref cursor [return return_type].

syntax of cursor:- declare
cursor cursor name select statement..

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions