Q

Why and how are ref cursors used?

I am not clear about the utility of a REF cursor. Why, where and when are they used?

I am not clear about the utility of a REF cursor. Why, where and when are they used?
Ref cursors can be used to access the contents of a database table. Also ref cursors can be opened dynamically. An example:
Declare
   type rc is ref cursor;
   
   cursor c is select * from dual;

   l_cursor rc;
begin
   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 dual;
   end if;
   open c;
end;
/
Note: The regular will only select data from dual, whereas the ref cursor can be opened using multiple cursors.

For more information see Brian Peasland's article on SearchOracle.com dated 23 December 2002, titled "REF cursors explained."

This was first published in July 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close