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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top