subject: The Difference Between Cursor And Ref Cursor Is Integral To Oracle Development [print this page] On an extremely technical basis most Oracle developers in India are aware that both cursor and ref cursor are the same. They are both kind of cursors that can be processed in the same manner. Some of the salient differences that exists between ref cursors and regular cursors are as follows:
1.It is possible to open ref cursor dynamically and can be defined at runtime where a regular cursor is static because it is defined at compile time.
2.It is possible to pass a ref cursor to PL or SQL routine or even sent back to a client while a regular cursor has to be addressed in a direct manner and cannot be returned to a client application
3.A ref cursor cannot be cached open through PL/SQL as is the case with regular cursor. This means that ref cursor features a parsing penalty
4.A ref cursor has to use explicit array fetching while a regular cursor works through implicit array fetch, which works 100 rows at a time
5.It is possible to define regular cursor outside a procedure function and is part of the global package variable but this is not the case with a ref cursor that has to be local in scope for a chunk of PL/SQL code.
Oracle development team create the coding for each of the above mentioned instances in the following manner:
1.Constructing a regular cursor and a ref cursor
Declare
type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
-- here we have a ref cursor whose definition is determined at runtime.
if ( to_char(sysdate,'dd') = 30 ) then
open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
open l_cursor for select * from dept;
else
open l_cursor for select * from dual;
end if;
-- Here there is a regular cursorand its definition was decided at compile time and cannot change
open c;
end;
/
This block of code depicts the most basic difference between cursors where it doesnt matter how many times the block is run, cursor C will always be SELECT * FROM DUAL. The ref cursor (L_CURSOR) on the other hand can be anything. Another aspect that is highlighted through this block of code is that it is possible to open ref cursor with a query that is constructed at runtime or even with a query that is predefined at compile time.
Where the second point is concerned, a ref cursor can be passed to another PL/SQL routine or even returned to a client but a regular cursor has to be addressed directly and it is not possible to give it back to a client application.
Code Listing 2: The feature of opening, passing and returning a ref cursor is implemented by the Oracle developers in India through the following coding steps:
