[Chapter 1] 1.4 Using Packages

1.4 Using Packages

Whether you are referencing an element in a builtin package, prebuilt package, or build-your-own package, the syntax is the same. One thing to remember is that a package itself is not any kind of executable piece of code. Instead, it is a repository for code that is executed or otherwise used. When you use a package, you actually execute or make reference to an element in a package. To use a package you must know what is defined and available inside the package. This information is contained in the package specification.

1.4.1 The Package Specification

The package specification contains the definition or specification of all elements in the package that may be referenced outside of the package. These are called the public elements of the package. Here is a very simple package specification consisting of two procedures:

PACKAGE sp_timer
IS
   PROCEDURE capture;
   PROCEDURE show_elapsed;
END sp_timer;

(The sp_timer package is an early version of the PLVtmr package, used to time PL/SQL code execution.) What do you learn by looking at this specification? That you can call either the capture or the show_elapsed procedures of sp_timer -- and that is it.

The package specification contains all the code needed for a developer to understand how to call the objects in the package. A developer should never have to examine the code behind the specification (which is the body) in order to understand how to use and benefit from the package.

Here is a more generic representation of the syntax for a package specification:

PACKAGE package_name 
IS
   [ declarations of variables and types ]
   [ headers of cursors ]
   [ headers of procedures and functions ]
END [ package_name ];

You can declare variables and include headers of both cursors and modules (and only the specifications). You must have at least one declaration or header statement in the package specification.

Notice that the package specification has its own BEGIN-END block syntax. This establishes a named context for all the elements of the package and allows them to exist outside of any particular PL/SQL block, such as a procedure or function.

Now let's take a look at a more complex package specification and use that as a springboard to learn how to execute and reference package-based PL/SQL code. Example 1.1 shows the specification for the pets_r_us package, which is used by veterinarians to keep track of their patients and to determine when a pet needs another visit.

Example 1.1: The specification of the pets_r_us package

PACKAGE pets_r_us 
IS
   max_pets_in_facility CONSTANT INTEGER := 120;
   pet_is_sick EXCEPTION;
   next_appointment DATE := SYSDATE;
   CURSOR onepet_cur (pet_id_in IN INTEGER) RETURN pet%ROWTYPE;
   CURSOR allpets_cur IS SELECT pet_id, name, owner FROM pet;
   FUNCTION next_pet_shots (pet_id_in IN NUMBER) RETURN DATE;
   PROCEDURE set_schedule (pet_id_in IN NUMBER);
END pets_r_us;

The pets_r_us package specification shown in Example 1.1 declares a constant, an exception, a variable, two cursors, a function, and a procedure. The constant informs us that the package restricts the number of pets allowed in the facility to 120. The pets_r_us package also provides an exception that can be used throughout the application to indicate that a pet is sick. It offers a predefined variable to hold the date of the next appointment and initializes that variable to today's date.

The code in this package might look odd to you; only the headers are present for the function and procedure. The executable code for these modules is, in fact, hidden in the package body (explored later in this chapter). A package specification never contains executable statements; you should not have to see this code in order to understand how to call the program.

Notice the difference between the two cursors. The first cursor, onepet_cur , takes a single parameter (primary key for a pet) and returns a record with the same structure as the pet table. The SELECT statement for this query is not, however, present. Instead, the query is hidden in the package body (the SQL is, after all, the implementation of the cursor) and the RETURN clause is used. In the second cursor, the RETURN clause is replaced by the actual query for the cursor. You can take either approach to cursors in packages.

1.4.2 Referencing Package Elements

A package owns its elements, just as a table owns its columns. An individual element of a package only makes sense, in fact, in the context of the package. Consequently, you use the same dot notation employed in "table.column" syntax for "package.element". Let's take a look at this practice by calling elements of the pets_r_us package.

In the following IF statement, I check to see if I am allowed to handle more than 100 pets in the facility:

IF pets_r_us.max_pets_in_facility > 100
   THEN
      ...
   END IF;

In this exception section, I check for and handle the situation of a sick pet:

EXCEPTION
   WHEN pets_r_us.pet_is_sick
   THEN
      ...
END;

I can open the cursor defined in a package by prefixing the package name to the cursor and passing any required arguments:

OPEN pets_r_us.onepet_cur (1305);

In the following statement, I assign (to the package variable next_appointment ) the date for the next shot for a pet identified by an Oracle Forms host variable (indicated by the use of the : before the block.item name):

:pets_r_us.next_appointment 
      := pets_r_us.next_pet_shots (:pet_master.pet_id);

And if you forget to qualify a package element with its package name? The compiler will try to find an unqualified element (table, standalone procedure, etc.) with the same name and characteristics. Failing that, your code will not compile.

1.4.2.1 Unqualified package references

There is one exception to the rule of qualifying a package element with its package name. Inside the body of a package, you do not need to qualify references to other elements of that package. PL/SQL will automatically resolve your reference within the scope of the package; the package is the "current" context. Suppose, for example, that the set_schedule procedure of pets_r_us (defined in the package specification) references the max_pets_in_facility constant. Such a reference would be unqualified , as shown below in the partial implementation of set_schedule (found in the package body):

PROCEDURE set_schedule (pet_id_in IN NUMBER) 
IS
   total_pets NUMBER := pet_analysis.current_load;
BEGIN
   ...
   IF total_pets < max_pets_in_facility 
   THEN
      ...
   END IF;
END;

There is no need to preface the "maximum pets" constant with pets_r_us . There is a need, on the other hand, to prefix the reference to the current_load function of the pet_analysis package.

1.4.3 The Memory-Resident Architecture of Packages

To use packages most effectively, you must understand the architecture of these constructs within an Oracle Server instance. Figure 1.2 shows how the different elements of shared memory are employed to support both package code and data.

Figure 1.2: The architecture of packages in shared memory

Before exploring the relationships in Figure 1.2 , keep these basic principles in mind:

The compiled code for stored objects (procedures, functions, and packages) is shared by all users of the instance with execute authority on that code.
Each Oracle session has its own copy of the in-memory data defined within stored objects.
The Oracle Server applies a least-recently used (LRU) algorithm to maintaining compiled code in shared memory.

When a user executes a stored program or references a package-based data structure, the PL/SQL runtime engine first must make sure that the compiled version of that code is available in the System Global Area or SGA of the Oracle instance. If the code is present in the SGA, it is then executed for that user. If the code is not present, the Oracle Server reads the compiled code from disk and loads it into the shared memory area. At that point the code is available to all users with execute authority on that code.

So if session 1 is the first account in the Oracle instance to reference package A, session 1 will cause the compiled code for A to be loaded into shared memory. When session 2 references an element in package A, that code is already present in shared memory and is re-used.

A user's relationship to data structures defined in stored code, particularly package data, is very different from that of the compiled code. While the same compiled code is shared, each user gets her own version of the data. This process is clear for procedures and functions. Any data declared in the declaration section of these programs is instantiated, manipulated, and then, on the termination of that program, erased. Every time a user calls that procedure or function, she gets her own local versions of the data.

The situation with packages is the same as that with stored code, but is less obvious at first glance. Data declared at the package level (defined outside of any particular procedure or function in the package) persist for as long as a session is active -- but those data are specific to a single Oracle session or connection. Each Oracle session is assigned its own private PL/SQL area, which contains a copy of the package data. This private PL/SQL area is maintained by the PL/SQL runtime engine for as long as your session is running. When session 1 references package A, session 1 instantiates her own version of the data structures used by A. When session 2 calls a program in A or accesses a data structure defined by A, session 2 gets her own copy of that data. Any changes made to the memory-based package data in session 1 is not affected by and does not affect the data in session 2.

NOTE: If you are running a multithreaded Oracle Server, then Figure 1.2 changes slightly. With the multithreaded architecture, the program global areas for each user are also stored within the SGA of the Oracle instance.

1.4.3.1 Managing packages in shared memory

When a package is loaded into shared memory, a contiguous amount of memory is required to hold the package (the same is true for any piece of stored code). So if you have a large package, you may have to tune your shared pool in the SGA to accommodate this package. (The shared pool is the area in the SGA devoted to shared SQL and PL/SQL statements.) You can get more space for your stored code by increasing the value of the SHARED_POOL_SIZE parameter in the database instance initialization file.[ 2 ]

[2] If the Oracle Server is having trouble fitting your stored code into memory, you will get ORA-04031 errors: out of shared memory.

The Oracle Server uses a least-recently used (LRU) algorithm to decide which items in the shared pool will remain present. If your package is flushed out of memory and is then needed by another program, the compiled code of the package will have to be read again from disk. Contiguous memory will also need to be available at that point.

If you know that you will want to use a large package or standalone program intermittently throughout application execution and do not want to have the code flushed out of memory, you can use the DBMS_SHARED_POOL package to pin your code into memory. The KEEP procedure of this package exempts the specified program or package from the LRU algorithm.

To pin the config package into shared memory, for example, you would execute this statement:

DBMS_SHARED_POOL.KEEP ('config');

You can also unpin a program with the UNKEEP program. The DBMS_SHARED_POOL package is not installed by default when you create an Oracle Server instance. You will need to execute (usually from within the SYS account) the dbmspool.sql script in the admin subdirectory of your particular version of the server. For example, on Windows95 and Oracle 7.2, you would issue this command in SQL*Plus:

SQL> @c:\orawin95\rdbms72\admin\dbmspool

You should only pin programs if absolutely necessary and unavoidable (you cannot, for instance, further expand the overall size of the SGA and the shared pool). Why? In answer, I quote from the above-mentioned dbmspool.sql file about KEEP:

--WARNING:  This procedure may not be supported in the future when
--and if automatic mechanisms are implemented to make this unnecessary.

You can calculate the size of a package or any other piece of stored code by executing queries against the USER_OBJECT_SIZE data dictionary view. This view contains information about the size of the source code, the size of the parsed code, and the size of the compiled code. The SQL statement below will display the names and sizes for any stored code larger than the specified SQL*Plus parameter:

SELECT name, type, source_size, parsed_size, code_size
  FROM user_object_size
 WHERE code_size > &1
 ORDER BY code_size DESC
/

1.4.4 Access to Package Elements

One of the most valuable aspects of a package is its ability to truly enforce information hiding. With a package you can not only modularize your secrets behind a procedural interface, you can keep those parts of your application completely private.

An element of a package, whether it is a variable or a module, can be either public or private:

Public: An element is public if it is defined in the specification. A public element can be referenced directly from other programs and PL/SQL blocks. The package specification is, in a sense, the gatekeeper for the package. It determines the package elements to which a developer may have access.
Private: An element is private if it is defined only in the body of the package, but does not appear in the specification. A private element cannot be referenced outside of the package. Any other element of the package may, however, reference and use a private element.

The distinction between public and private elements gives PL/SQL developers unprecedented control over their data structures and programs. Figure 1.3 shows a Booch[ 3 ] diagram for the package that displays private and public package elements, and very neatly portrays the way these two kinds of elements interact.

[3] This diagram is named after Grady Booch, who pioneered many of the ideas of the package, particularly in the context of object-oriented design.

Figure 1.3: A Booch diagram for a package

In Figure 1.3 , all of the boxes that lie completely inside the box are private elements, defined only within the body of the package. Boxes that lie on the boundary of the box are public elements, defined in the package specification and implemented (if programs) in the package body. An external program can make direct references only to those package elements that lie on the boundary. But any package element, whether wholly inside the boundary or straddling that line, can reference any other package element.

A boundary in the package delineates that which is publicly available and that which is private or hidden from view. It has particularly important and valuable consequences for data structures defined in a package.

1.4.4.1 Public and private data

Whether a variable is declared in the specification or body, it does function as a global piece of data. Once the package is instantiated in your session, data declared in the package persist for the duration of the session. A variable will retain its value until it is changed. That value will be available to any program that has access to the data. The kind of access depends on whether the variable is defined in the package specification or in the body.

To understand the consequences of public (specification-declared) data and private (body-declared) data in packages, consider the following simple package. In downsize , hire_date is a public variable and fire_date is a private variable.

PACKAGE downsize
IS
   v_hire_date DATE;
END;

PACKAGE BODY downsize
IS
   v_fire_date DATE;
END;

Since v_hire_date is defined in the package specification, I can directly reference that variable in my own code outside of the downsize package, as follows:

Read the value of the hire_date variable:
```
last_hired := downsize.v_hire_date;
```
Change the value of the hire_date variable to ten days in the future:
```
downsize.v_hire_date := SYSDATE + 10;
```

If I try to access v_fire_date in the same way, however, my code will not compile. It is hidden behind the public boundary of the package. Its value is maintained in my private global area since it is in a package, but the only programs that can reference it are those defined within the package itself, either in the body or the specification.

The next chapter covers the implications of public global data and contains recommendations on how to safeguard your application and data.

1.4.4.2 Switching between public and private

When you first create a package, your decision about which elements of a package are public and which private is not cast in stone. You can, in fact, switch a public element to private and vice versa at any time.

If you find that a private element program or cursor should instead be made public, simply add the header of that element to the package specification and recompile. It will then be visible outside of the package. Notice that you do not need to make any changes at all to the package body.

If you want to make a private variable accessible directly from outside the package, you will need to remove the declaration of that data structure from the body and paste it into the specification. You cannot declare the same element in both the body and the specification.

If you do make a public element private, you will need to remember that any program that referenced that element will no longer compile successfully.