The Difference Between Cursor And Ref Cursor Is Integral To Oracle Development

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:

SQL> declare

2 l_cursor sys_refcursor;

3 l_rec all_users%rowtype;

4 l_query varchar2(300) :=

5 'select *

6 from all_users

7 where rownum

by: Dylan Rodriguez

POST COMMENT

The World Of Online Shopping And The Convenience It Offers Ways To Save Your Bucks When Shipping And Delivery Your Cycle Save Your Time And Money By Shopping Shoes Online An Introduction To Iso And As9100 Training Importance Of Security And Confidentiality In Legal Transcription Dental Insurance And Emergency Dental Services Pros And Cons Of A Dental Assistant Diploma Hgh Deficiency Treatment For Both Adults And Kids Moving Services Customized To Your Needs And Budget Use Payroll Services To Give Your Employees The Best, And Clear Your Headache Eat As Much As You Want And Lose Weight Online Shopping - Pros And Cons Drug Rehabilitation Treatments And Facilities In Southern California