Ask the Expert

Difference between regular and REF cursors

I have a question regarding your answer to the following question, posed on 23 December 2002: "Can you throw some light on REF cursors? Are they the same as the usual cursors that we define in PL/SQL? If not, how do they differ and when can we use them?" At the end of your answer, you said, "But with REF cursors, you can pass the table and column to the function, and have the REF cursor build the cursor 'on the fly'."

Doesn't this mean that with REF cursors too, you need to know the table and column in advance to pass this to the function, and if so, then what's the real difference between regular and REF cursors?

    Requires Free Membership to View

With REF cursors, you do not need to know the table and column in advance. And this is one of the big powers of REF cursors over traditional cursors. For instance, you may want to select from one of many tables depending on your application logic. And in your procedure, you may want to dynamically set the cursor's table. This is where a REF cursor comes in. Look at the following code example:
   r_cursor refcur;
   TYPE tabtype IS TABLE OF VARCHAR2(100);
   r_val tabtype;
   sql_stmt VARCHAR2(2000);
   -- Formulate the SQL statement based on the column
   -- and table name passed as inputs to this proc
   sql_stmt:='SELECT '||c_name||' FROM '||t_name;

   -- Instantiate the Ref Cursor
   OPEN r_cursor FOR sql_stmt;

   -- Bulk collect into the Ref Cursor
   FETCH r_cursor INTO r_val;

   CLOSE r_cursor

   -- Perform other logic on the return results

In the above example, I used a REF cursor to dynamically define the cursor's query. With traditional cursors, the cursor's query needs to be known beforehand to be able to compile the stored proc.

Tom Kyte has a lot of information on REF cursors on his Web site. I highly recommend his site for this type of question.

This was first published in November 2005

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: