Oracle in a Nutshell

REF CURSOR Variables

A CURSOR points to a specific result set, while a REF CURSOR variable can be used to point to different result sets. A REF CURSOR can also have the SQL it uses assigned in the execution section of a PL/SQL block, which means you can use it to implement dynamic SQL statements.

The cursor and result set are objects in the database. The result set is maintained as long as a database cursor points to it. For this reason, you can use a REF CURSOR to pass result sets of data between different PL/SQL program units. You can also use REF CURSOR variables to hide minor variations in queries.

The syntax for a REF_CURSOR type is:

TYPE ref_cursor_name IS REF CURSOR 
   [RETURN record_type];

where record_type is a row that exists in the database.

If you do not include a RETURN clause, then you are declaring a weak REF CURSOR. REF CURSOR variables declared from weak REF CURSORs can be associated with any query at runtime. A REF CURSOR declaration with a RETURN clause defines a strong REF CURSOR. A REF CURSOR variable based on a strong REF CURSOR can be associated with queries whose result sets match the number and datatype of the record structure after the RETURN at runtime.

To use REF CURSOR variables, you must first create a REF_CURSOR type, then declare an instance based on that type. The following example shows the declaration of both weak and strong REF CURSORs:

DECLARE -- Create a cursor type based on the companies table. TYPE company_curtype IS REF CURSOR RETURN companies%ROWTYPE; ...