Fetch and Close a REF Cursor

FETCH a REF cursor:

  FETCH {cursor_name | :host_cursor_variable_name}
     INTO {variable1[, variable2,...] | record_name};

The variables must match (both in number and positionally) the 
columns listed in the REF cursor OPEN statement.
Also the data types must either match or be compatible.

A fetch statement retrieves rows one at a time from
the result set of a multi-row query - in other words it
advances the cursor to the next row.

CLOSE a REF cursor:

  CLOSE {cursor_name | :host_cursor_variable_name};

Closing a cursor releases the context area. 

REF Cursor Attributes:

cursor%ROWCOUNT  - int - number of rows affected by last SQL statement
cursor%FOUND    - bool - TRUE if >1 row returned
cursor%NOTFOUND - bool - TRUE if 0 rows returned
cursor%ISOPEN   - bool - TRUE if cursor still open 

Typically the REF CURSOR definition and the
OPEN FOR SELECT will be in a packaged procedure on the server

A client-side application will then call the procedure
- thus obtaining a valid open cursor with the correct SQL
The client-side application will then perform further
processing.. FETCH into variables etc

Note that the cursor variable must be the same TYPE
for both the packaged procedure on the server and
in the DECLARE section of the client-side application.

The way to be sure of this is to declare the TYPE in a
PACKAGE

Example:

CREATE PACKAGE my_cursor_types AS

   TYPE MyCursor IS REF CURSOR;
   ...
END my_cursor_types;


CREATE PROCEDURE GetCarter ( proc_cv IN OUT my_cursor_types.MyCursor,
                             emp_name VARCHAR2(50) )
   ...

Then the client-side application code would start like

DECLARE
    local_cv        my_cursor_types.MyCursor;
    carter_record   carter%ROWTYPE
BEGIN
   GetCarter(local_cv,:employee)    -- employee is a host variable
   FETCH local_cv INTO carter_record;
   ...

Related Commands:

Fetch and CLOSE a normal cursor

Related Views: