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?
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