Creating Packages   «Prev 

Steps to Execute a package’s procedure

  1. You must query the USER_SOURCE view to observe the package specifications. I have filled in the SELECT clause. You must now fill in the WHERE clause. Type in the appropriate text and press Enter.
  2. The lesson simulation shows you how to set your environment so that output displays on the screen. Type in the appropriate SQL*Plus SET command now and press Enter.
  3. I have defined appropriate variables to test the procedure. Your job is to type in the correct call command for the procedure and press Enter. Note: Specify sales id 2 as the incoming parameter.
  4. I have completed the PL/SQL block. This one is somewhat different than the lesson example because the data is modified in the database rather than returned as values or parameters from the procedure. So, I have queried the modified table and then displayed the values that were changed by the procedure. Now, execute the PL/SQL block and view the results.
  5. The results are now displayed on the screen. The four values that were updated by the procedure are displayed for you to view.


Rules for Calling Packaged Elements

It does not really make any sense to talk about running or executing a package, since a package 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. Below are some examples.
The following package specification declares a constant, an exception, a cursor, and several modules: 
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_appointment 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:
  1. When you reference elements defined in a package specification from outside of that package (an external program), you must use dot notation in the form package_name.element_name.
  2. When you reference package elements from within the package (specification or body), you do not need to include the name of the package. PL/SQL will automatically resolve your reference within the scope of the package.