Chapter 12. Cursor Variables

Cursor variables, first available in PL/SQL Version 2.3, are reference pointers to a cursor’s result set. The main advantage of a cursor variable is that it allows us to easily pass the result of a query among different PL/SQL blocks, such as procedures or functions. This chapter tests your ability to define and use cursor variables in your applications.

Beginner

12-1.

Which of the following statements describe a cursor variable?

  1. A SELECT statement whose WHERE clause can be varied each time the query is opened in a PL/SQL program.

  2. A pointer to the result set of a cursor (a SELECT statement that has been opened in a PL/SQL program).

  3. An explicit cursor declared in the specification of a package.

12-2.

What are the two steps you must take to declare a cursor variable?

12-3.

Which of the following declarations of REF CURSOR types are valid, and which are invalid?

  1. TYPE my_type IS REF CURSOR;

  2. TYPE empdept_t IS REF CURSOR JOINED emp, dept;

  3. TYPE living_wage_t IS REF CURSOR RETURN (SELECT salary FROM employee);

  4. TYPE all_orders_cvt IS REF CURSOR RETURN orders%ROWTYPE;

12-4.

Given the following definitions of the table and REF CURSOR:

CREATE TABLE environmental_disaster (
    company_responsible VARCHAR2(100),
    occurred_on DATE;
);

DECLARE
   TYPE disasters_cvt IS REF CURSOR RETURN environmental_disaster%ROWTYPE;

Which of the declarations of cursor variables are valid, and which are invalid?

  1. exxon_valdez disasters_cvt;

  2. union_carbide_in_bhopal disasters_cvt%ROWTYPE;

  3. love_canal environmental_disaster%ROWTYPE; ...

Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial