Oracle 10g Release2 new functionality ref Cursor

REF cursor is the reference of the result set that we define on the server side. When we open a REF cursor, no data is returned to the client, instead, the address of the data on the server will be returned to the client. This allows the user to decide at what time and in that way to fetch the data through REF CURSOR.

In previous versions of Odp.net, we could fetch data through REF CURSOR, but we could not pass REF CURSOR as an input parameter to pl/sql stored procedures and stored functions. However, in Oracle Database 10g Release2, we can simply pass REF CURSOR as input parameter to pl/sql stored procedures and stored functions. This is a new feature of Oracle Database 10g Release2.

We'll introduce you to this new feature in a routine way.

Preparing the Database

We're going to generate a table and a package in the database, and we'll use that in the next example.

Please log in to the database with an HR user and run the following script.
create table processing_result
(
　status varchar2(64)
);
create or replace package cursor_in_out as
type emp_cur_type is ref cursor return employees%rowtype;
procedure process_cursor(p_cursor in emp_cur_type);
end;
/
create or replace package body cursor_in_out as
procedure process_cursor(p_cursor in emp_cur_type) is
employee employees%rowtype;
begin
　loop
　　fetch p_cursor into employee;
　　exit when p_cursor%notfound;
　　insert into processing_result
　　values('Processed employee #' ||
　　employee.employee_id || ': ' ||
　　employee.first_name || ' ' ||
　　employee.last_name);
　end loop;
end;
end;
/