Skip to Main Content
Oracle in a Nutshell
book

Oracle in a Nutshell

by Rick Greenwald, David C. Kreines
December 2002
Intermediate to advanced content levelIntermediate to advanced
928 pages
85h 29m
English
O'Reilly Media, Inc.
Content preview from 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; ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Internals

Oracle Internals

Donald K. Burleson
Oracle PL/SQL

Oracle PL/SQL

Lewis Cunningham
Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Rick Greenwald, Robert Stackowiak, Jonathan Stern

Publisher Resources

ISBN: 0596003366Errata Page