Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: