Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

Expert

12-17.

Suppose you define a package with a procedure that opens a cursor variable as shown:

CREATE OR REPLACE PACKAGE dizmee
AS
   TYPE swallow_florida_cvt IS
      REF CURSOR RETURN hickey_grouse%ROWTYPE;
   PROCEDURE open_wide (yum_cv swallow_florida_cvt);
END dizmee;
/
CREATE OR REPLACE PACKAGE BODY dizmee
AS
   PROCEDURE open_wide (yum_cv swallow_florida_cvt)
   IS
   BEGIN
      OPEN yum_cv FOR SELECT * FROM hickey_grouse;
   END open_wide;
END dizmee;
/

Why does the package body fail to compile?

12-18.

None of the following blocks compile. Explain the problems.

  1. DECLARE
       TYPE one_ref_cur IS REF CURSOR;
       one_cv one_ref_cur;
    BEGIN
       OPEN one_cv FOR SELECT * FROM employee;
       display_employees@hong_kong_office (one_cv);
  2. DECLARE
       TYPE one_ref_cur IS REF CURSOR;
       first_cv one_ref_cur;
       second_cv one_ref_cur;
    BEGIN
       OPEN first_cv FOR SELECT * FROM employee;
       second_cv := first_cv;
       IF first_cv != second_cv THEN
  3. DECLARE
       TYPE one_ref_cur IS REF CURSOR;
       TYPE list_of_cvs IS TABLE OF one_ref_cur;
    BEGIN
       OPEN one_cv FOR SELECT * FROM employee FOR UPDATE;
  4. DECLARE
       TYPE emp_cvt IS REF CURSOR RETURN emp%ROWTYPE;
       TYPE tmp_cvt IS REF CURSOR RETURN emp%ROWTYPE;
       PROCEDURE open_emp_cv (
          emp_cv IN OUT emp_cvt, tmp_cv IN OUT tmp_cvt)
       IS
       BEGIN
          emp_cv := tmp_cv;
       END;

12-19.

Which of the following declarations allows you to work with REF CURSORs in SQL*Plus?

  1. SQL> VARIABLE cv REFCURSOR

  2. SQL> VARIABLE cv REF CURSOR

  3. SQL> VARIABLE cv CUR VARIABLE

12-20.

The following scenario is used for this question and the next. Suppose you are building a repository of ...

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 Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page