Intermediate

15-13.

The DBMS_UTILITY.GET_TIME function returns the number of hundredths of seconds that have elapsed since a point in time in the past. You can use this function to calculate the elapsed time of your program’s execution. Here is the kind of script you might write to figure out how long it takes to run calc_totals:

DECLARE
   time_before BINARY_INTEGER;
   time_after BINARY_INTEGER;
BEGIN
   time_before := DBMS_UTILITY.GET_TIME;
   calc_totals;
   time_after := DBMS_UTILITY.GET_TIME;
   p.l (time_after - time_before);
END;

Create a package that allows you to rewrite the above block of code as follows:

BEGIN
   timer.capture;
   calc_totals;
   timer.show_elapsed;
END;

so the code displays the following form of output:

Elapsed time: 2.43 seconds

15-14.

This package specification doesn’t compile. What is the problem?

CREATE OR REPLACE PACKAGE curvar
IS
   TYPE cv_t IS REF CURSOR RETURN employee%ROWTYPE;
   emp_cv cv_t;
END curvar;
/

15-15.

(For Oracle Developer users only) Consider the following package specification defined in the database:

CREATE OR REPLACE PACKAGE emp_rules
IS
   latest_birthday DATE;
   emp_too_young EXCEPTION;
   FUNCTION too_young (birthdate_in IN DATE) RETURN BOOLEAN;
END curvar;
/

Which of the following client-side blocks (defined in Oracle Reports or Oracle Forms, for example) don’t compile?

  1. BEGIN
IF emp_rules.latest_birthday > ADD_MONTHS (SYSDATE, -216)
   THEN
      MESSAGE ('Employees must be at least 18 years old.');
   END IF;
END;
  2. BEGIN IF emp_rules.too_young (:empblock.birthdate) > ADD_MONTHS (SYSDATE, ...

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