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 a package specification from outside of that package (an external program), you must usedot notation in the form package_name.element_name.

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

17.4 Working with Package Data

Package data consists of variables and constants that are defined at the package level—that is, not within a particular function or procedure in the package. The scope of the package data is therefore not a single program, but rather the package as a whole. In the PL/SQL runtime architecture, package data structures persist (hold their values) for the duration of a session (rather than the duration of execution for a particular program).

If package data is declared inside the package body, then that data persists for the session but can be accessed only by elements defined in the package itself (private data).

If package data is declared inside the package specification, then that data persists for the session and is directly accessible (to both read and modify the value) by any program that has EXECUTE authority on that package (public data). Public package data is very similar to and potentially as dangerous as GLOBAL variables in Oracle Forms.

If a packaged procedure opens a cursor, that cursor remains open and is available throughout the session. It is not necessary to define the cursor in each program. One module can open a cursor while another performs the fetch. Additionally, package variables can carry data across the boundaries of transactions because they are tied to the session rather than to a single transaction.