Q

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?

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?

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:
CREATE OR REPLACE PROCEDURE test_proc (t_name IN VARCHAR2,c_name IN
VARCHAR2)
AS
   TYPE refcur IS REF CURSOR;
   r_cursor refcur;
   TYPE tabtype IS TABLE OF VARCHAR2(100);
   r_val tabtype;
   sql_stmt VARCHAR2(2000);
BEGIN
   -- 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

END;
/
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

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close