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

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, ...

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