Oracle PL/SQL Programming, Third Edition

Rules for Calling Packaged Elements

It doesn’t really make any sense to talk about running or executing a package (after all, it is just a container for code elements). However, you will certainly want to run or reference those elements defined in a package.

A package owns its objects, just as a table owns its columns. To reference an element defined in the package specification outside of the package itself, you must use the same dot notation to fully specify the name of that element. Let’s look at some examples.

The following package specification declares a constant, an exception, a cursor, and several modules:

CREATE OR REPLACE PACKAGE pets_inc 
IS
   max_pets_in_facility CONSTANT INTEGER := 120;
   pet_is_sick EXCEPTION;

   CURSOR pet_cur (pet_id_in IN pet.id%TYPE) RETURN pet%ROWTYPE;

   FUNCTION next_pet_shots (pet_id_in IN pet.id%TYPE) RETURN DATE;
   PROCEDURE set_schedule (pet_id_in IN pet.id%TYPE);

END pets_inc;

To reference any of these objects, I preface the object name with the package name, as follows:

DECLARE
   -- Base this constant on the id column of the pet table.
   c_pet CONSTANT pet.id%TYPE:= 1099;
   v_next_apppointment DATE;
BEGIN
   IF pets_inc.max_pets_in_facility > 100
   THEN
      OPEN pets_inc.pet_cur (c_pet);
   ELSE
      v_next_appointment:= pets_inc.next_pet_shots (c_pet);
   END IF;
EXCEPTION
   WHEN pets_inc.pet_is_sick
   THEN
      pets_inc.set_schedule (c_pet);
END;

To summarize, there are two rules to follow in order to reference and use elements in a package:

• When you reference elements defined in ...