A package consists of up to two chunks of code: the specification (required) and the body (optional, but almost always present). The specification defines how a developer can use the package: which programs can be called, what cursors can be opened, and so on. The body contains the implementation of the programs (and, perhaps, cursors) listed in the specification, plus other code elements as needed.

Suppose that I need to write code to retrieve the “full name” of an employee whose name is in the form “last, first.” That seems easy enough to write out:

CREATE OR REPLACE PROCEDURE process_employee (
   employee_id_in IN employee.employee_id%TYPE)
IS
   l_fullname VARCHAR2(100);
BEGIN
   SELECT last_name || ',' || first_name
     INTO l_fullname
     FROM employee
    WHERE employee_id = employee_id_in;
    ...
END;

Yet there are many problems lurking in this seemingly transparent code:

What’s a developer to do? I would like to be able to change the way I write my code to avoid the above hardcodings. To do that, I need to write these things once (one definition of a “full name” datatype, one representation of the formula, one version of the query) and then call them wherever needed. Packages to the rescue!

Take a look at the following package specification:

/* Files on web: fullname.pks, fullname.pkb, fullname.tst */
1    CREATE OR REPLACE PACKAGE employee_pkg
2    AS
3        SUBTYPE fullname_t IS VARCHAR2 (200);
4     
5        FUNCTION fullname (
6           last_in  employee.last_name%TYPE,
7           first_in  employee.first_name%TYPE)
8           RETURN fullname_t;
9     
10        FUNCTION fullname (
11           employee_id_in IN employee.employee_id%TYPE)
12           RETURN fullname_t;
13    END employee_pkg;

What I have done here is essentially list the different elements I want to use. On line 3, I declare a “new” datatype using SUBTYPE called fullname_t. It is currently defined to have a maximum of 200 characters, but that can be easily changed if needed.

I then declare a function called fullname (lines 5 through 8). It accepts a last name and a first name and returns the full name. Notice that the way the full name is constructed is not visible in the package specification. That’s a good thing, as you will soon see.

Finally, on lines 10-12, I have a second function, also called fullname; this version accepts a primary key for an employee and returns the full name for that employee. This repetition is an example of overloading , which we explored in Chapter 17.

Now, before I even show you the implementation of this package, let’s rewrite the original block of code using my packaged elements (notice the use of dot notation, which is very similar to its use in the form table.column):

DECLARE
   l_name employee_pkg.fullname_t;
   employee_id_in   employee.employee_id%TYPE := 1;
BEGIN
   l_name := employee_pkg.fullname (employee_id_in);
   ...
END;

I declare my variable using the new datatype, and then simply call the appropriate function to do all the work for me. The name formula and the SQL query have been moved from my application code to a separate “container” holding employee-specific functionality. The code is cleaner and simpler. If I need to change the formula for last name or expand the total size of the full name datatype, I can go to the package specification or body, make the changes, and recompile any affected code, and the code will automatically take on the updates.

Speaking of the package body, here is the implementation of employee_pkg:

1    CREATE OR REPLACE PACKAGE BODY employee_pkg
2    AS
3       FUNCTION fullname (
4          last_in employee.last_name%TYPE,
5          first_in employee.first_name%TYPE
6       )
7          RETURN fullname_t
8       IS
9       BEGIN
10          RETURN    last_in || ',' || first_in;
11       END;
12     
13       FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)
14          RETURN fullname_t
15       IS
16          retval   fullname_t;
17       BEGIN
18          SELECT fullname (last_name, first_name) INTO retval
19            FROM employee
20           WHERE employee_id = employee_id_in;
21     
22          RETURN retval;
23       EXCEPTION
24          WHEN NO_DATA_FOUND THEN RETURN NULL;
25     
26          WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop;
27       END;
28    END employee_pkg;

Lines 3-11 are nothing but a function wrapper around the last-comma-first formula. Lines 13-27 showcase a typical single-row query lookup built around an implicit query. Notice, though, that on line 18, the query calls that self-same fullname function to return the combination of the two name components.

So now if my users call and say “first-space-last, please!”, I will not groan and work late into the night, hunting down occurrences of || ', ' ||. Instead, I will change the implementation of my employee_pkg.fullname in about five seconds flat and astound my users by announcing that they are good to go.

And that, dear friends, gives you some sense of the beauty and power of packages.

Before diving into the details of package syntax and structure, you should be familiar with a few concepts:

Let’s go back to the public-private dichotomy for a moment. The distinction drawn between public and private elements in a package gives PL/SQL developers unprecedented control over their data structures and programs. A fellow named Grady Booch came up with a visual way to describe this aspect of a package (now called, naturally, the Booch diagram).

Take a look at Figure 18-1. Notice the two labels Inside and Outside. Outside consists of all the programs you write that are not a part of the package at hand (the external programs). Inside consists of the package body (the internals or implementation of the package).

Figure 18-1. Booch diagram showing public and private package elements

Here are the conclusions we can draw from the Booch diagram:

The specification of a package lists all the elements in that package that are available for use in applications, and provides all the information a developer needs in order to use elements defined in the package (often referred to as an API or application programming interface). A developer should never have to look at the implementation code in a package body to figure out how to use an element in the specification.

Here are some rules to keep in mind for package specification construction:

END my_package;

Here is a very simple package specification illustrating these rules:

1    CREATE OR REPLACE PACKAGE favorites_pkg
2       AUTHID CURRENT_USER
3    IS
4       -- Two constants; notice that I give understandable
5       -- names to otherwise obscure values.
6     
7       c_chocolate CONSTANT PLS_INTEGER := 16;
8       c_strawberry CONSTANT PLS_INTEGER := 29;
9     
10       -- A nested table TYPE declaration
11       TYPE codes_nt IS TABLE OF INTEGER;
12     
13       -- A nested table declared from the generic type.
14       my_favorites codes_nt;
15     
16       -- A REF CURSOR returning favorites information.
17       TYPE fav_info_rct IS REF CURSOR RETURN favorites%ROWTYPE;
18     
19       -- A procedure that accepts a list of favorites
20       -- (using a type defined above) and displays the
21       -- favorite information from that list.
22       PROCEDURE show_favorites (list_in IN codes_nt);
23     
24       -- A function that returns all the information in
25       -- the favorites table about the most popular item.
26       FUNCTION most_popular RETURN fav_info_rct;
27     
28    END favorites_pkg; -- End label for package

As you can see, a package specification is, in structure, essentially the same as a declaration section of a PL/SQL block. One difference, however, is that a package specification may not contain any implementation code.

The package body contains all the code required to implement the package specification. A package body is not always needed; see the section "When to Use Packages" for examples of package specifications without bodies. A package body is required when any of the following conditions is true:

Structurally, a package body is very similar to a procedure definition. Here are some rules particular to package bodies:

Here is an implementation of the favorites_pkg body:

CREATE OR REPLACE PACKAGE BODY favorites_pkg
IS
   -- A private variable
   g_most_popular   PLS_INTEGER := c_strawberry;

   -- Implementation of procedure
   PROCEDURE show_favorites (list_in IN codes_nt) IS
   BEGIN
      FOR indx IN list_in.FIRST .. list_in.L*
      LOOP
         DBMS_OUTPUT.put_line (list_in (indx));
      END LOOP;
   END show_favorites;

   -- Implement the function
   FUNCTION most_popular RETURN fav_info_rct
   IS
      retval fav_info_rct;
      null_cv fav_info_rct;
   BEGIN
      OPEN retval FOR
      SELECT *
        FROM favorites
       WHERE code = g_most_popular;
      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN null_cv;
   END most_popular;

END favorites_pkg; -- End label for package

See the section "When to Use Packages" for other examples of package bodies.

Packages can contain data structures that persist for your entire session (this topic is covered in more detail in the later section, "Working with Package Data“). The first time your session uses a package (whether by calling a program defined in the package, reading or writing a variable, or using a locally declared variable TYPE), Oracle initializes that package. This involves one or all of the following steps:

Oracle executes these steps just once per session, and not until you need that information (i.e., on the “first touch” of that package).

ORA-04068: existing state of packages has been discarded

The initialization section of a package consists of all the statements following the BEGIN statement at the end of the package (and outside any procedure or function’s definitions) and through to the END statement for the entire package body. Here is what an initialization section in favorites_pkg might look like:

CREATE OR REPLACE PACKAGE BODY favorites_pkg
IS
   g_most_popular   PLS_INTEGER;

   PROCEDURE show_favorites (list_in IN codes_nt) ... END;

   FUNCTION most_popular RETURN fav_info_rct ... END;

   PROCEDURE analyze_favorites (year_in IN INTEGER) ... END;

-- Initialization section
BEGIN
   g_most_popular := c_chocolate;

   -- Use new Oracle9i Database EXTRACT to get year number from SYSDATE!
   analyze_favorites (EXTRACT (YEAR FROM SYSDATE));
END favorites_pkg;

The initialization section is a powerful mechanism: PL/SQL automatically detects when this code should be run. You do not have to explicitly execute the statements, and you can be sure that they are run only once. Why would you use an initialization section? The following sections explore some specific reasons.

CREATE OR REPLACE PACKAGE BODY company IS
BEGIN
   /*
   || Initialization section of company_pkg updates the global
   || package data of adifferent package. This is a no-no!
   */
   SELECT SUM (salary)
     INTO employee_pkg.max_salary
     FROM employee;
END company;

/* File on web: valerr.pkg */
CREATE OR REPLACE PACKAGE valerr
IS
   FUNCTION get RETURN VARCHAR2;
END valerr;
/
CREATE OR REPLACE PACKAGE BODY valerr
IS
   -- A package-level, but private global variable
   v VARCHAR2(1) := 'ABC';

   FUNCTION get RETURN VARCHAR2
   IS
   BEGIN
      RETURN v;
   END;
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Before I show you v...'),

EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.PUT_LINE ('Trapped the error!'),
  
END valerr;

SQL>exec DBMS_OUTPUT.PUT_LINE (valerr.get)
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

SQL>BEGIN
  2     DBMS_OUTPUT.PUT_LINE ('V is set to ' || NVL (valerr.get, 'NULL'));
  3  END;
  4  /
V is set to NULL

CREATE OR REPLACE PACKAGE BODY valerr
IS
   v VARCHAR2(1);

   FUNCTION get RETURN VARCHAR2 IS BEGIN ... END;
BEGIN
   v := 'ABC';

EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.PUT_LINE ('Error initializing valerr:'),
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
  
END valerr;

CREATE OR REPLACE PACKAGE BODY <package_name>
IS
   -- Place private data structures below.
   -- Avoid assigning default values here.
   -- Instead, assign in the initialization procedure and
   --   verify success in the verification program.
  
   -- Place private programs here.
  
   -- Initialization section (optional)
   PROCEDURE initialize IS
   BEGIN
      NULL;
   END initialize;
  
   PROCEDURE verify_initialization (optional)
   -- Use this program to verify the state of the package.
   -- Were default values assigned properly? Were all
   -- necessary steps performed?
   IS
   BEGIN
      NULL;
   END verify_initialization;
  
   -- Place public programs here.
  
BEGIN
   initialize;
   verify_initialization;
END <package_name>;
/

As a result of the SGA architecture, package data structures act like globals within the PL/SQL environment. Remember, however, that they are accessible only within a single Oracle session or connection; package data is not shared across sessions. If you need to share data between different Oracle sessions, you can use the DBMS_PIPE package or Oracle Advanced Queuing. (See O’Reilly’s Oracle Built-In Packages or Oracle documentation for more information about these facilities.)

You need to be careful about assuming that different parts of your application maintain a single Oracle connection. There are times when a tool may establish a new connection to the database to perform an action. If this occurs, the data you have stored in a package in the first connection will not be available.

For example, suppose that an Oracle Forms application has saved values to data structures in a package. When the form calls a stored procedure, this stored procedure can access the same package-based variables and values as the form can because they share a single Oracle connection. But now suppose that the form kicks off a report using Oracle Reports. By default, Oracle Reports uses a second connection to the database (with the same username and password) to run the report. Even if this report accesses the same package and data structures, the values in those data structures will not match those used by the form. The report is using a different Oracle connection and a new instantiation of the package data structures.

Just as there are two types of data structures in a package (public and private), there are also two types of global package data to consider: global public data and global private data. The next two sections explore the differences between these kinds of package data.

Any data structure declared in the specification of a package is a global public data structure, meaning that any program outside of the package can access it. You can, for example, define a PL/SQL table in a package specification and use it to keep a running list of all employees selected for a raise. You can also create a package of constants that are used throughout all your programs. Other developers will then reference the packaged constants instead of hardcoding the values in their programs. You are also allowed to change global public data structures unless they are declared as CONSTANTs in the declaration statement.

Global data is the proverbial “loose cannon” of programming. It is very convenient to declare and is a great way to have all sorts of information available at any point in time. However, reliance on global data structures leads to unstructured code that is full of side effects.

Recall that the specification of a module should give you all the information you need to understand how to call and use that module. However, it is not possible to determine if a package reads and/or writes to global data structures from the package’s specification. Because of this, you cannot be sure of what is happening in your application and which program changes what data.

It is always preferable to pass data as parameters in and out of modules. That way, reliance on those data structures is documented in the specification and can be accounted for by developers. On the other hand, you should create named global data structures for information that truly is global to an application, such as constants and configuration information.

You can put all such data into a single, central package, which would be easiest to manage. Note, however, that such a design also builds a “single point of recompilation” into your application: every time you make a change to the package and recompile the specification, you will cause many programs in your application to be invalidated.

One particularly interesting type of package data is the explicit cursor, which was introduced in Chapter 14. I can declare a cursor in a package, in either the body or the specification. The state of this cursor (i.e., whether it is opened or closed, the pointer to the location in the result set) persists for the session, just like any other packaged data. This means that it is possible to open a packaged cursor in one program, fetch from it in a second, and close it in a third. This flexibility can be an advantage and also a potential problem.

Let’s first look at some of the nuances of declaring packaged cursors , and then move on to how you can open, fetch, and close such cursors.

/* File on web: pkgcur.sql */
1    CREATE OR REPLACE PACKAGE book_info
2    IS
3       CURSOR byauthor_cur (
4          author_in   IN   books.author%TYPE
5       )
6       IS
7          SELECT *
8            FROM books
9           WHERE author = author_in;
10     
11       CURSOR bytitle_cur (
12          title_filter_in  IN   books.title%TYPE
13       ) RETURN books%ROWTYPE;
14     
15       TYPE author_summary_rt IS RECORD (
16          author                        books.author%TYPE,
17          total_page_count              PLS_INTEGER,
18          total_book_count              PLS_INTEGER);
19     
20       CURSOR summary_cur (
21          author_in   IN   books.author%TYPE
22       ) RETURN author_summary_rt;
23    END book_info;

1    CREATE OR REPLACE PACKAGE BODY book_info
2    IS
3       CURSOR bytitle_cur (
4          title_filter_in   IN   books.title%TYPE
5       ) RETURN books%ROWTYPE
6       IS
7           SELECT *
8           FROM books
9           WHERE title LIKE UPPER (title_filter_in);
10     
11       CURSOR summary_cur (
12          author_in   IN   books.author%TYPE
13       ) RETURN author_summary_rt
14       IS
15          SELECT author, SUM (page_count), COUNT (*)
16            FROM books
17           WHERE author = author_in;
18    END book_info;

20/11    PLS-00323: subprogram or cursor '<cursor>' is declared in a
         package specification and must be defined in the package body

5/13     PLS-00400: different number of columns between cursor SELECT
         statement and return value

DECLARE
   onebook   book_info.bytitle_cur%ROWTYPE;
BEGIN
   OPEN book_info.bytitle_cur ('%PL/SQL%'),

   LOOP
      EXIT WHEN book_info.bytitle_cur%NOTFOUND;
      FETCH book_info.bytitle_cur INTO onebook;
      book_info.display (onebook);
   END LOOP;

   CLOSE book_info.bytitle_cur;
END;

BEGIN -- Only open...
   OPEN book_info.bytitle_cur ('%PEACE%'),
END;

ORA-06511: PL/SQL: cursor already open

/* File on web: openclose.sql */
CREATE OR REPLACE PACKAGE personnel
IS
   CURSOR emps_for_dept (
      deptno_in IN employee.department_id%TYPE)
   IS
      SELECT * FROM employee
       WHERE department_id = deptno_in;

   PROCEDURE open_emps_for_dept(
      deptno_in IN employee.department_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE close_emps_for_dept;

END personnel;

DECLARE
   one_emp personnel.emps_for_dept%ROWTYPE;
BEGIN
   personnel.open_emps_for_dept (1055);

   LOOP
      EXIT WHEN personnel.emps_for_dept%NOTFOUND;
      FETCH personnel.emps_for_dept INTO one_emp;
      ...
   END LOOP;

   personnel.close_emps_for_dept;
END;

As we have seen, package data by default persists for your entire session (or until the package is recompiled). This is an incredibly handy feature, but it has some drawbacks:

To help you manage the use of memory in packages, PL/SQL offers the SERIALLY_REUSABLE pragma. This pragma, which must appear in both the package specification and the body (if one exists), marks that package as serially reusable. For such packages, the duration of package state (the values of variables, the open status of a packaged cursor, etc.) can be reduced from a whole session to a single call of a program in the package.

To see the effects of this pragma, consider the following book_info package. I have created two separate programs: one to fill a list of books and another to show that list.

/* File on web: serialpkg.sql */
CREATE OR REPLACE PACKAGE book_info
IS
   PRAGMA SERIALLY_REUSABLE;
   PROCEDURE fill_list;

   PROCEDURE show_list;
END;

As you can see in the following package body, that list is declared as a private, but global, associative array:

/* File on web: serialpkg.sql */
CREATE OR REPLACE PACKAGE BODY book_info
IS
   PRAGMA SERIALLY_REUSABLE;

   TYPE book_list_t IS TABLE OF books%ROWTYPE INDEX BY BINARY_INTEGER;

   my_books   book_list_t;

   PROCEDURE fill_list IS
   BEGIN
      FOR rec IN (SELECT * FROM books WHERE AUTHOR LIKE '%FEUERSTEIN%')
      LOOP
         my_books (NVL (my_books.L*, 0) + 1) := rec;
      END LOOP;
   END fill_list;

   PROCEDURE show_list IS
   BEGIN
      IF my_books.COUNT = 0
      THEN
         DBMS_OUTPUT.put_line ('** No books to show you...'),
      ELSE
         FOR indx IN my_books.FIRST .. my_books.L*
         LOOP
            DBMS_OUTPUT.put_line (my_books (indx).title);
         END LOOP;
      END IF;
   END show_list;
END;

To see the effect of this pragma, I fill and then show the list. In my first approach, these two steps are done in the same block, so the collection is still loaded and can be displayed:

SQL>BEGIN
  2     DBMS_OUTPUT.put_line (
  3        'Fill and show in same block:'
  4     );
  5     book_info.fill_list;
  6     book_info.show_list;
  7  END;
  8  /
Fill and show in same block:
Oracle PL/SQL Programming
Oracle PL/SQL Best Practices
Oracle PL/SQL Built-in Packages

In my second attempt, I fill and show the list in two separate blocks. As a result, my collection is now empty:

SQL>BEGIN
  2      DBMS_OUTPUT.put_line ('Fill in first block'),
  3      book_info.fill_list;
  4  END;
  5  /
Fill in first block

SQL> BEGIN
  2     DBMS_OUTPUT.put_line ('Show in second block:'),
  3     book_info.show_list;
  4  END;
  5  /
Show in second block:
** No books to show you...

Here are some things to keep in mind for serialized packages:

Rather than have developers write their own SQL statements, you should provide an interface to those SQL statements. This is one of the most important motivations for building packages, yet is only rarely employed by developers.

With this approach, PL/SQL developers as a rule will not write SQL in their applications. Instead, they will call predefined, tested, and optimized code that does all the work for them; for example, an “add” procedure (overloaded to support records) that issues the INSERT statement and follows standard error-handling rules; a function to retrieve a single row for a primary key; and a variety of cursors that handle the common requests against the data structure (which could be a single table or a “business entity” consisting of multiple tables).

If you take this approach, developers will not necessarily need to understand how to join three or six different highly normalized tables to get the right set of data. They can just pick a cursor and leave the data analysis to someone else. They will not have to figure out what to do when they try to insert and the row already exists. The procedure has this logic inside it (and can be easily upgraded to use the MERGE statement introduced in Oracle9i Database Release 1).

Perhaps the biggest advantage of this approach is that as your data structures change, the maintenance headaches of updating application code are both minimized and centralized. The person who is expert at working with that table or object type makes the necessary changes within that single package, and the changes are then “rolled out” more or less automatically to all programs relying on that package.

Data encapsulation is a big topic and can be very challenging to implement in a comprehensive way. You will find an example of a table encapsulation package (built around the employee table) in the employee_tp.pks, employee_qp.*, employee_cp.*, department_tp.pks,and department_qp.* files on the book’s web site (these files were generated by Qnxo, http://www.qnxo.com).

Let’s take a look at what kind of impact this use of packages can have on your code. The givebonus1.sp file on the book’s web site contains a procedure that gives the same bonus to each employee in the specified department, but only if he has been with the company for at least six months. Here are the parts of the give_bonus program that contains the SQL (see givebonus1.sp for the complete implementation):

/* File on web: givebonus1.sp */
CREATE OR REPLACE PROCEDURE give_bonus (
    dept_in IN employee.department_id%TYPE,
    bonus_in IN NUMBER)
IS
    v_name VARCHAR2(50);

    CURSOR by_dept_cur
    IS
       SELECT *
         FROM employee
        WHERE department_id = dept_in;
BEGIN
    /* Retrieve all information for the specified department. */
    SELECT name
      INTO v_name
      FROM department
     WHERE department_id = dept_in;

    /* For each employee in the specified department... */
    FOR rec IN by_dept_cur
    LOOP
       IF employee_rp.eligible_for_bonus (rec)
       THEN
          UPDATE employee
             SET salary = rec.salary + bonus_in
           WHERE employee_id = rec.employee_id;
       END IF;
    END LOOP;
 
    ... more processing with name and other elements
END;

Now let’s compare that to the encapsulation alternative, which you will find in its entirety in givebonus2.sp:

/* File on web: givebonus2.sp */
1    CREATE OR REPLACE PROCEDURE give_bonus (
2       dept_in    IN   employee_tp.department_id_t
3     , bonus_in   IN   employee_tp.bonus_t
4    )
5    IS
6       l_department     department_tp.department_rt;
7       l_employees      employee_tp.employee_tc;
8       l_row            PLS_INTEGER;
9       l_rows_updated   PLS_INTEGER;
10    BEGIN
11       l_department := department_qp.onerow (dept_in);
12       l_employees := employee_qp.ar_fk_emp_department (dept_in);
13     
14       l_row := l_array.FIRST;
15     
16       WHILE (l_row IS NOT NULL)
17       LOOP
18          IF employee_rp.eligible_for_bonus (rec)
19          THEN
20             employee_cp.upd_onecol_pky
21                (colname_in          => 'salary'
22               , new_value_in        =>   l_employees (l_row).salary
23                                        + bonus_in
24               , employee_id_in      => l_employees (l_row).employee_id
25               , rows_out            => l_rows_updated
26                );
27          END IF;
28     
29          l_row := l_employees.NEXT (l_row);
30       END LOOP;
31     
32       ... more processing with name and other elements
33    * END;

Here is an explanation of the changes made in this second version:

Overall, the SQL statements have been removed from the program and have been replaced with calls to reusable procedures and functions. Doing so optimizes the SQL in my application and allows me to write more robust code in a more productive manner.

It is by no means a trivial matter to build (or generate) such packages, and I recognize that most of you will not be willing or able to adopt a 100% encapsulated approach. You can, however, gain many of the advantages of data encapsulation without having to completely revamp your coding techniques. At a minimum, I suggest that you:

Virtually any application has a variety of magic values—literal values that have special significance in a system. These values might be type codes or validation limits. Your users will tell you that these magic values never change. “I will always have only 25 line items in my profit-and-loss,” one will say. “The name of the parent company,” swears another, “will always be ATLAS HQ.” Don’t take these promises at face value, and never code them into your programs. Consider the following IF statements:

IF footing_difference BETWEEN 1 and 100
THEN
   adjust_line_item;
END IF;

IF cust_status = 'C'
THEN
   reopen_customer;
END IF;

You are begging for trouble if you write code like this. You will be a much happier developer if you instead build a package of named constants as follows:

CREATE OR REPLACE PACKAGE config_pkg
IS
   closed_status     CONSTANT VARCHAR2(1) := 'C';
   open_status       CONSTANT VARCHAR2(1) := 'O';
   active_status     CONSTANT VARCHAR2(1) := 'A';
   inactive_status   CONSTANT VARCHAR2(1) := 'I';

   min_difference    CONSTANT PLS_INTEGER := 1;
   max_difference    CONSTANT PLS_INTEGER := 100;

   earliest_date     CONSTANT DATE := SYSDATE;
   latest_date       CONSTANT DATE := ADD_MONTHS (SYSDATE, 120);

END config_pkg;

Using this package, my two IF statements above now become:

IF footing_difference
   BETWEEN config_pkg.min_difference and config_pkg.max_difference
THEN
   adjust_line_item;
END IF;

IF cust_status = config_pkg.closed_status
THEN
   reopen_customer;
END IF;

If any of my magic values ever change, I simply modify the assignment to the appropriate constant in the configuration package. I do not need to change a single program module. Just about every application I have reviewed (and many that I have written) mistakenly includes hardcoded magic values in the program. In every single case (especially those that I myself wrote!), the developer had to make repeated changes to the programs, during both development and maintenance phases. It was often a headache, and sometimes a nightmare; I cannot emphasize strongly enough the importance of consolidating all magic values into one or more packages.

You will find another example of such a package in the utl_file_constants.pkg file. This package takes a different approach from that shown above. All values are hidden in the package body. The package specification consists only of functions, which return the values. This way, if and when I need to change a value, I do not have to recompile the package specification, and I avoid the need to recompile dependent programs.

Some of Oracle’s own utilities, such as UTL_FILE and DBMS_OUTPUT, leave lots to be desired. We all have our pet peeves, and not just about how Oracle builds utilities for us. What about that “ace” consultant who blew into town last year? Are you still trying to deal with the code mess he left behind? Maybe you can’t replace any of this stuff, but you can certainly consider building your own package on top of theirs (their packages, their poorly designed data structures, etc.) to correct as many of the problems as possible.

Rather than fill up the pages of this book with examples, I’ve listed the filenames of a number of packages available on the book’s web site as companion code to this text. These demonstrate this use of packages and also offer some useful utilities. I suggest that you look through all the *.pkg files on the site for other code you might find handy in your applications.

If you have a dozen procedures and functions that all revolve around a particular feature or aspect of your application, put them into a package so that you can manage (and find) that code more easily. This is most important when coding the business rules for your application. When implementing business rules, follow these important guidelines:

Before you start building an application, construct a series of packages that encapsulate all of its rules. Sometimes these rules are part of a larger package, such as a table encapsulation package. In other cases, you might establish a package that contains nothing but the key rules. Here is one example:

/* File on web: custrules.pkg */
CREATE OR REPLACE PACKAGE customer_rules
IS
    FUNCTION min_balance RETURN PLS_INTEGER;  /* Toronto */

    FUNCTION eligible_for_discount
       (customer_in IN customer%ROWTYPE)
       RETURN BOOLEAN;

    FUNCTION eligible_for_discount
       (customer_id_in IN customer.customer_id%TYPE)
       RETURN BOOLEAN;

END customer_rules;

The “eligible for discount” function is hidden away in the package so that it can be easily managed. I also use overloading to offer two different interfaces to the formula: one that accepts a primary key and establishes eligibility for that customer in the database, and a second that applies its logic to customer information already loaded into a %ROWTYPE record. Why did I do this? Because if a person has already queried the customer information from the database, he can use the %ROWTYPE overloading and avoid a second query.

Of course, not all “logically related functionality” has to do with business rules. I might need to add to the built-in string manipulation functions of PL/SQL. Rather than create 12 different standalone functions, I will create a “string enhancements” package and put all of the functions there. Then I and others know where to go to access that functionality.

Take advantage of persistent package data to improve the response time of your application by caching (and not requerying) static data. You can do this at a number of different levels; for each of the following items, I’ve listed a few helpful code examples available on the book’s web site:

Use the test files to compare cached and noncached performance.

If you decide to take advantage of this technique, remember that this data is cached separately for each session that references the package. This means that if your cache of a row in a table consumes 20K and you have 1,000 users, you have just used up 2 MB of real memory in your system—in addition to all the other memory consumed by Oracle.