REF CURSORS

REF CURSORS

Since Oracle 7.3 REF CURSORS have been available which allow recordsets to be returned from stored procedures, functions and packages. Lets say that we have the bank transaction tables like this, transaction_2005, transaction_2006 that stores the transactions of the respective years. (Both the tables have identical columns). Now the input to the procedure would be the year, and based on the year, we have to process the records of that table. In this case, we might want to use a cursor and the catch is that the table name to be used in the select statement of the cursor is not known until runtime. In this case, the way the cursor is declared, opened and fetched are also different as shown in the example below.

CREATE OR REPLACE PROCEDURE Process_transaction (p_year number) AS

Type RefC is REF CURSOR;

Cur_Transaction RefC;
TransactionStmt Varchar2(1000);
Transaction_fetch varchar2(1000);
Transaction_record Transaction_2006%RowType;
Year_no char(4);
l_return number;

BEGIN
Year_no := to_char(p_year) ;
TransactionStmt := 'SELECT * FROM transaction_'|| Year_no ;

Cur_Transaction := (RefC) dbms_sql.open_cursor; -- Opening the cursor.
dbms_sql.parse(Cur_Transaction, TransactionStmt, 1); -- Parsing the cursor for the select statement
l_return := dbms_sql.execute_and_fetch(Cur_Transaction);
Loop
Fetch Cur_Transaction into Transaction_record;
Exit when Cur_Transaction %NOTFOUND;
---

--- Do the processing with the data in the cursor.

---

end loop;
END: