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 fetched so far
cursor%ROWTYPE  - returns the datatype of the underlying table
cursor%FOUND    - bool - TRUE if >1 row returned
cursor%NOTFOUND - bool - TRUE if 0 rows returned
cursor%ISOPEN   - bool - TRUE if cursor still open

Notes:
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

Cursor%ROWCOUNT will display the number of rows retrieved so far.
Until Oracle has retrieved all the rows then by definition it does not have an accurate record of how many there are. Of course user_tables or dba_tables will have a count of the number of rows (NUM_ROWS), but this is only as up to date as the statistics.

Some examples of selecting the nth row or the last nth row (discussion threads)

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:

Fetch and CLOSE a normal cursor