Expert

15-22.

Suppose you define the following package:


/* Filename on web page onecur.sql */
CREATE OR REPLACE PACKAGE onecur
IS
   CURSOR onerow (
      id_in IN employee.employee_id%TYPE)
   IS
      SELECT * FROM employee
       WHERE employee_id = id_in;
END onecur;
/

You then create the following procedure, procA:

CREATE OR REPLACE PROCEDURE procA
IS
BEGIN
   OPEN onecur.allrows (1005);
END procA;
/

Next, you create the procedure procB:

CREATE OR REPLACE PROCEDURE procB
IS
BEGIN
   OPEN onecur.allrows (2356);
   procA;
END procB;
/

What happens when you execute procB?

15-23.

Rewrite the package in 15-22 so that it provides procedures to open and close the cursor, ensuring that a user never receives a “cursor already open” error when opening the cursor and never receives an “invalid cursor” error when closing the cursor.

15-24.

Every time you reference the USER function to retrieve the currently connected username, you do a SELECT FROM dual. On a 300-MHz laptop, 10,000 consecutive calls to USER take approximately 2.5 seconds to complete. While this is not a long time, it’s not exactly fast either. How could you modify the loop so that the USER function is called only once?

15-25.

The following package won’t compile because it’s too big: proc1 contains 20 KB of source code, and proc2 contains 22 KB. How can you redefine this code so that the call interface remains the same, but you can still call toobig.proc1 and toobig.proc2 ...

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.