O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Intermediate

Q:

15-13.

This code allows you to deduce (and create) the package specification:


/* Filename on web page: timer.pkg */
CREATE OR REPLACE PACKAGE timer
IS
   PROCEDURE capture;
   PROCEDURE show_elapsed;
END timer;
/

To create the body, you need to not only define the two procedures but also create a global data structure to hold the “time before” you’ll use to subtract from the “time after.” Here is the complete package:


/* Filename on web page: timer.pkg */
CREATE OR REPLACE PACKAGE BODY timer
IS
   last_timing INTEGER := NULL;

   PROCEDURE capture IS
   BEGIN
     last_timing := DBMS_UTILITY.GET_TIME;
   END;

   PROCEDURE show_elapsed IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (
        'Elapsed time: ' ||
        (DBMS_UTILITY.GET_TIME - last_timing)/100);
   END;

END timer;
/

Q:

15-14.

You cannot declare a cursor variable as part of a package; you can only declare a cursor variable within PL/SQL blocks.

Q:

15-15.

For each of the examples, the block:

  1. Does not compile. Within Oracle Developer (at least through version 2.1), you can reference only stored packaged elements if the elements are functions or procedures. You can’t reference constants, variables, TYPEs, or exceptions.

  2. Compiles. This block is fine.

  3. Does not compile. See explanation for (a).

Q:

15-16.

Here is one possible way to add trace and validation capabilities to the sessval ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required