15-1.

Is it possible to execute a package?

15-2.

If I want to call a procedure named “calc_totals” in the “financial_pkg” package, how would I write the code?

15-3.

What are the exceptions to the dot-notation rule for packaged elements? In other words, when don’t you need to qualify a package element with its package name?

15-4.

List all the packages referenced in the following piece of code, and say what types of packaged elements are used:

DECLARE
   v_new_pet pet.pet_id%TYPE;
   v_last_appointment DATE;
BEGIN
   IF pets_r_us.max_pets_in_facility >
         TO_NUMBER (v_current_count)
   THEN
      /* Add another pet ... */
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Facility is full'),
   END IF;
EXCEPTION
   WHEN pets_r_us.pet_is_sick
   THEN
      ...
   WHEN NO_DATA_FOUND
   THEN
      RAISE_APPLICATION_ERROR (
         -20555, 'Pet not found'),
END;

15-5.

Write a package specification that contains a DATE variable that retains its value (i.e., persists) for the duration of your session.

15-6.

Write a package that allows a developer to read and write the value of a DATE package variable through “get and set” programs.

15-7.

Which of the following cursor declarations, which appear in a package specification called empdata, are valid?

15-8.

Why would you want to hide the SELECT statement of a packaged cursor inside the package body?

15-9.

Does every package need to have both a specification and a body?

15-10.

For which of the following package specifications is a package body required or not required?

15-11.

The following code snippet contains a hardcoded value (the maximum date allowed in the application). Rewrite this code to take advantage of a global, named constant that hides the value, and implement that named constant. How can you implement this constant so that if the value for the maximum date changes, you don’t have to recompile programs that contain a reference to the constant?

IF v_date > TO_DATE ('31-DEC-2010', 'DD-MON-YYYY')

15-12.

Is it possible to design a package so that the first time a session tries to use anything in the package (run a program, reference a variable, use a TYPE), code is run to initialize the package?

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?

15-16.

Modify the following package so that whenever a program attempts to change the value of mydate, the program displays (on the screen) the current value, the new value, and the execution call stack so that you can see what program is attempting the change. Also, make sure that the new date is never set into the future.

CREATE OR REPLACE PACKAGE sessval
IS
   PROCEDURE set_mydate (date_in IN DATE);
   FUNCTION mydate RETURN DATE;
END sessval;
/
CREATE OR REPLACE PACKAGE BODY sessval
IS
   g_mydate DATE;

   PROCEDURE set_mydate (date_in IN DATE)
   IS
   BEGIN
      g_mydate := date_in;
   END;

   FUNCTION mydate RETURN DATE
   IS
   BEGIN
      RETURN g_mydate;
   END;
END sessval;
/

15-17.

Enhance the following package so that you only see the trace output when requested. As an added wrinkle, you are not allowed to change the definition of set_mydate.

/* Filename on web page: sessval1.pkg */
CREATE OR REPLACE PACKAGE sessval
IS
   PROCEDURE set_mydate( date_in IN DATE );

   FUNCTION mydate RETURN DATE;
END sessval;
/
CREATE OR REPLACE PACKAGE BODY sessval
IS
   g_mydate DATE;

   PROCEDURE set_mydate( date_in IN DATE) IS
   BEGIN
      IF    date_in IS NULL
         OR date_in > SYSDATE
      THEN
         DBMS_OUTPUT.put_line (
            'Sessval.mydate cannot be set into the future.'
         );
      ELSE
         DBMS_OUTPUT.put_line (
            'Current value of sessval.mydate: ' ||
            g_mydate
         );
         DBMS_OUTPUT.put_line (
            'New value of sessval.mydate: ' ||
            date_in
         );
         DBMS_OUTPUT.put_line (
            DBMS_UTILITY.format_call_stack
         );
         g_mydate := date_in;
      END IF;
   END;

   FUNCTION mydate RETURN DATE IS
   BEGIN
      RETURN g_mydate;
   END;
END sessval;
/

15-18.

The following package allows you to display the elapsed time of program execution using DBMS_OUTPUT.PUT_LINE. What if you want to use this utility in an environment that does not easily integrate with DBMS_OUTPUT and uses some other mechanism to display information? Modify the timer package so that you can retrieve the elapsed time without having to display it:

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

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;
/

15-19.

Write a package that calculates and displays a person’s age. The user should be able to provide his or her date of birth as a date, a number, or a string.

15-20.

The DBMS_OUTPUT.PUT_LINE procedure allows PL/SQL developers to print output from their programs on the screen (standard output). There are a number of problems associated with the package, including the following:

Create a package to corrects these problems.

15-21.

Can you declare the same data structure in a package specification and its package body?

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 to get your work done?

/* Filename on web page: splitpkg.pkg */
CREATE OR REPLACE PACKAGE toobig
IS
   PROCEDURE proc1;
   PROCEDURE proc2;
END;
/
CREATE OR REPLACE PACKAGE BODY toobig
IS
   PROCEDURE proc1
   IS
   BEGIN
      /* lots of code */
      NULL;
   END;

   PROCEDURE proc2
   IS
   BEGIN
      /* lots more code */
      NULL;
   END;
END;
/

15-26.

Does the following package specification contain a valid implementation of overloading? Specifically, does the package specification compile? If so, can you actually run either of the programs successfully?

/* Filename on web page: sales.pkg */
CREATE OR REPLACE PACKAGE salespkg
IS
   PROCEDURE calc_total (zone_in IN VARCHAR2);
   PROCEDURE calc_total (reg_in IN VARCHAR2);
END salespkg;
/

15-27.

Suppose you want more flexibility than DBMS_OUTPUT can provide for your tracing and debugging needs. For example, in some circumstances, you want to see the output from your program while it is still running; most of the time, though, you’re quite content with output to the screen. You also want to see the execution call stack when your trace program is called to see where you are in the process. Create a package that offers a replacement for the built-in procedure, DBMS_OUTPUT.PUT_LINE, with the options mentioned. Build a body to fit the following specification for such a package:

/* Filename on web page: watch.pkg */
CREATE OR REPLACE PACKAGE watch
IS
   /* Direct output to the screen; the default. */
   PROCEDURE toscreen;

   /* Direct output to a pipe so it can be viewed even
      while the program is still running. */
   PROCEDURE topipe;

   /* Watch a specific action; the replacement for the
      DBMS_OUTPUT.PUT_LINE procedure. */
   PROCEDURE action (prog IN VARCHAR2, val IN VARCHAR2);

   /* Show the contents of the database pipe. */
   PROCEDURE show;
END;

15-28.

When you want to execute a SQL statement constructed at runtime (dynamic SQL), you must call the DBMS_SQL.EXECUTE function. If your SQL statement is an INSERT, UPDATE, or DELETE, this function returns the number of rows modified by the statement. Otherwise, the return value is ignored. This means that if you execute a query or a DDL statement or a PL/SQL block—anything except a DML statement—you must declare a variable to hold a return value and then not use it, as shown:

DECLARE
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
   feedback INTEGER;
BEGIN
   DBMS_SQL.PARSE (cur, 'TRUNCATE TABLE employee', DBMS_SQL.NATIVE);
   feedback := DBMS_SQL.EXECUTE (cur);
   DBMS_SQL.CLOSE (cur);
END;

Do what Oracle should have done: create a package that offers an implementation of the EXECUTE program that allows developers to avoid the need to declare a “feedback” variable unless they are executing DML.

15-29.

Suppose that you have set up a database table to store standard user configuration information. These configuration values are changed only when users are off the system; they do not change during an active session. Here is the definition of the table:

/* Filename on web page: usrcnfg.ins */
CREATE TABLE user_config (
   username VARCHAR2(30),
   cubicle# VARCHAR2(20),
   max_coffee_breaks INTEGER
   );

Furthermore, suppose you use this package to retrieve this information:

/* Filename on web page: usrcnfg1.pkg */
CREATE OR REPLACE PACKAGE userconfig
IS
   FUNCTION cubicle# RETURN VARCHAR2;
   FUNCTION max_coffee_breaks RETURN INTEGER;
END userconfig;
/
CREATE OR REPLACE PACKAGE BODY userconfig
IS
   FUNCTION cubicle# RETURN VARCHAR2
   IS
      retval user_config.cubicle#%TYPE;
   BEGIN
      SELECT user_config.cubicle#
        INTO retval
        FROM user_config
        WHERE username = USER;
      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN 'SECURITY DESK';
   END;

   FUNCTION max_coffee_breaks RETURN INTEGER
   IS
      retval user_config.max_coffee_breaks%TYPE;
   BEGIN
      SELECT user_config.max_coffee_breaks
        INTO retval
        FROM user_config
        WHERE username = USER;
      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN 0;
   END;
END userconfig;
/

What are the problems with the design of this package? How would you change the package to improve its performance? As you design your changes, remember that only the package body should change, so that existing calls to the usrcnfg package aren’t affected.

15-30.

As a general rule, when you define data structures in a package, but not within any procedure or function of the package, this data persists for your entire session. What feature in Oracle8i Release 8.1 allows you to have your package data treated like “local” data (i.e., to discard memory and reinstantiate data structures with each block execution)?