6.3.1 The Package Specification

The specification defines the package's API, which governs every aspect of how the package is used. The specification lists the headers of the procedures and functions in the API, as well as any variables, types, cursors, or constants necessary to interface with the package. These last items are global variables, accessible both from inside and outside the package. The headers and declarations in the specification are called public elements because they are the interface between the package and the outside world.

You might see the specification as an afterthought, if not a downright nuisance, that stands in the way of your real work. Nothing could be further from the truth. The success or failure of a package almost always depends on a clean, crisp interface that is simple to understand and use.

6.3.2 The Package Body

The package body contains the actual code for the modules in the specification. It must include a complete implementation of each of these modules. Additionally, it can also include procedures and functions not listed in the specification, as well as declarations for variables, types, cursors, and constants. These elements, invisible to the outside world, are private because they can be referenced only from within the body itself.

6.3.3 Example

Designing a good package takes practice. A package should be rich enough to support a wide variety of complex activities, yet simple enough to grasp quickly. As with any other art, the best place to learn package design is from the classics, so in this section we'll create a stack package based on an index-by table. Here's the specification:

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PACKAGE stack
IS
   
   /*------------------------------------------------------
   || Global type declaration
   */------------------------------------------------------
   TYPE stack_array IS TABLE OF VARCHAR2(2000)
      INDEX BY BINARY_INTEGER;
   
   /*-------------------------------------------------------
   || Global API declaration
   */-------------------------------------------------------
   -- Initialize the stack
   PROCEDURE init;
   
   -- Push an item onto the stack
   PROCEDURE push (item IN VARCHAR2);
   
   -- Return the first element on the stack
   FUNCTION pop
      RETURN VARCHAR2;
   
   -- Return a boolean if the stack is empty
   FUNCTION is_empty
      RETURN BOOLEAN;
   
   -- Copy the stack into an array
   PROCEDURE copy_to_array (
      s OUT stack_array,
      num_elements OUT NUMBER
      );

END stack;

Our next step is to actually implement each of the procedures and functions listed in the specification. Here's the body of the stack package:

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PACKAGE BODY stack
IS
   
   /*------------------------------------------------------
   || Local declarations
   */------------------------------------------------------
   -- Declare stack data structure
   local_stack stack_array;   
   -- Declare index to top of stack
   top NUMBER DEFAULT 0;

   /*-------------------------------------------------------
   || Implementation
   */-------------------------------------------------------

   /*
   || Initialize the stack
   */
   PROCEDURE init
   IS
   BEGIN
      top := 0;
   END init;
   
   /*
   || Push an item onto the stack.
   || Since PL/SQL arrays are unconstrained, we never have    
   || to worry about pushing too many elements!
   */
   PROCEDURE push (item IN VARCHAR2)
   IS
   BEGIN
      top := top + 1;
      local_stack  (top) := item;
   END push;
   
   /*
   || Return a boolean if the stack is empty
   */
   FUNCTION is_empty
      RETURN BOOLEAN
   IS
   BEGIN
      IF top = 0
      THEN
         RETURN TRUE;
      ELSE
         RETURN FALSE;
      END IF;
   END is_empty;
   
   /*
   || Return the first element on the stack. 
   || Return NULL if the stack is empty.
   */
   FUNCTION pop
      RETURN VARCHAR2
   IS
      item_to_return VARCHAR2(2000);
   BEGIN
      IF is_empty
      THEN
         item_to_return := NULL;
      ELSE
         item_to_return := local_stack (top);
         top := top - 1;
      END IF;
      RETURN item_to_return;
   END pop;
   
   /*
   || Copy the stack into an array
   */
   PROCEDURE copy_to_array (
      s OUT stack_array,
      num_elements OUT NUMBER
      )
   IS
   BEGIN
      -- Set the number of elements
      num_elements := top;
      -- Load each element into the array
      FOR i IN 1 .. top
      LOOP
         s  (i) := local_stack (i);
      END LOOP;
   END copy_to_array;

END stack;

6.3.4 Using a Package

After we've created the package specification and body, we can start using it in other procedures and functions. You use the following syntax to refer to a public element (variable, procedure, function, etc.) in a package:

package_name.public_element_name

The following sample illustrates how to use the stack package developed in the last section:

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PROCEDURE test_stack
IS
   stack_copy stack.stack_array;
   stack_size NUMBER;
BEGIN
   --Push some test data onto the stack
   FOR i IN 1 .. 10
   LOOP
      stack.push (i);
   END LOOP;
   
   -- Make a copy of the stack
   
   stack.copy_to_array (stack_copy, stack_size);
   
   -- Pop all elements off the stack
   WHILE NOT stack.is_empty
   LOOP
      DBMS_OUTPUT.put_line (stack.pop);
   END LOOP;
   
   -- Print the copied elements
   FOR i IN 1 .. stack_size
   LOOP
      DBMS_OUTPUT.put_line (stack_copy (i));
   END LOOP;

END test_stack;

6.3.5 Information Hiding

You might have noticed in the stack example that we declared the local_stack and the top variable inside the body, rather than in the specification. This is an example of information hiding , a technique that's used to hide a package's implementation details from its users.

A package should be a black box; input comes in one side and predictable output goes out the other. The details between these steps should be invisible to everyone except the package's developer. It's amazing how often knowing how something works can get us into trouble. How many times have you written one program to take advantage of a bug in another? These shortcuts turn bugs into permanent fixtures. Information hiding eliminates the possibility of this problem by forcing everyone to use the package the way it was intended to be used.

Access to the internal workings of a package can cause problems even when the workaround seems perfectly innocent. To return to our original example, if the local_stack and top variables in the stack example were declared in the package specification, and not hidden away in the body, a developer in a rush might be tempted to write a program to bypass the stack directly, as you can see here:

/*
|| Quick procedure to print stack
*/
CREATE OR REPLACE PROCEDURE show_stack
   i NUMBER DEFAULT 0;
IS
BEGIN
   FOR i IN REVERSE 1 .. STACK.top
   LOOP
      DBMS_OUTPUT.put_line (STACK.local_stack (i));
   END LOOP;
END;

This shortcut depends on the stack being implemented as an index-by table. If we decided to redesign the package to represent the stack in another way (for example, using an object type we can store directly in the database), it would break this program and every other program that made a similar use of the package's public data structures.

6.3.6 Variable Persistence Throughout a Session

Package variables, whether they are declared in the specification or the body, maintain their values throughout a session. All values are initialized when a session begins and are lost when it ends. This is called persistence because the values remain, or persist, even when the package is not being directly used. Only package variables declared as part of the specification or body persist. Variables declared inside a procedure or function do not.

For example, suppose you log into SQL*Plus and push a few elements onto the stack. After that, you execute a few SELECT statements, issue some updates, and describe a table or two. Finally, before you're ready to log out, you call the test_stack procedure. When it executes, not only does it print "1" through "10," it also prints the elements you manually added at the beginning of the session. PL/SQL remembered the previous elements, even though you've been doing other things. However, if you logged out and reconnected before running the test_stack procedure, the previous elements disappear, because variables persist only throughout a single session.

In Chapter 7, The PL/SQL Toolkit , we'll look at some strategies to mimic persistence in the web environment.