1.3.1 What Is a Package?

This section offers a brief introduction to packages. You can find more detailed treatments in both Oracle PL /SQL Programming (O'Reilly & Associates, 1995 and 1997), and Advanced Oracle PL /SQL Programming with Packages (O'Reilly & Associates, 1996), my two previous books on PL /SQL.

A package is a collection of PL /SQL elements that are "packaged" or grouped together within a special BEGIN-END syntax, a kind of "meta-block" of code. Here is a partial list of the kinds of elements you can place in a package:

• Cursors

• Variables (scalars, records, tables, etc.) and constants

• Exception names and PRAGMAs for associating an error number with an exception

• PL /SQL table and record TYPE statements

• Procedures and functions

Packages are among the least understood and most underutilized features of PL /SQL. That's a shame, because the package structure is also one of the most useful constructs for building well-designed PL /SQL-based applications. Packages provide a structure in which you can organize your modules and other PL /SQL elements. They encourage proper structured programming techniques in an environment that often befuddles the implementation of structured programming. When you place a program unit into a package, you automatically create a "context" for that program. By collecting related PL /SQL elements in a package, you express that relationship in the very structure of the code itself. Packages are often called "the poor man's objects" because they support some, but not all, object-oriented rules.

The PL /SQL package is a deceptively simple yet powerful construct. It consists of up to two distinct parts: the specification and the body. The package specification defines the public interface or API (Application Programming Interface) of the package: those elements that can be referenced outside of the package. The package body contains the implementation of the package. In just a few hours you can learn the basic elements of package syntax and rules; there's not all that much to it. You can spend weeks and months, however, uncovering all the nuances and implications of the package structure.

Of course, if you are working with built-in packages, you can leave those details to Oracle. You just have to figure out how to make the best use of the packages provided.

1.3.2 Controlling Access with Packages

Probably the most important implication of package structure is how the builder of the package has complete control over what you can see or do. The users of a package can do either of the following:

• Execute programs listed in the package specification

• Reference elements (variables, constants, exceptions, etc.) listed in the package specification

What can't a user of a package do? You can't look inside the package and see how the code was implemented. You can't bypass the programs in the package specification in order to modify (corrupt) data structures managed inside the package body.

These restrictions are closely tied to the power and usefulness of the package structure. To illustrate this point, consider the following simple timer package. First, the specification:

PACKAGE tmr
IS
   PROCEDURE capture;
   PROCEDURE show_elapsed;
END tmr;

The tmr.capture procedure captures the current time. The tmr.show_elapsed procedure shows the elapsed time. The following script, for example, displays the amount of time it takes to run the calc_totals procedure:

BEGIN
   tmr.capture;
   calc_totals;
   tmr.show_elapsed;
END;
/

Now let's take a look at the package body (where all the code for those two procedures resides):

PACKAGE BODY tmr
IS
   last_timing NUMBER := NULL;

   PROCEDURE capture IS
   BEGIN
      last_timing := DBMS_UTILITY.GET_TIME;
   END;

   PROCEDURE show_elapsed IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.GET_TIME - last_timing);
   END;
END tmr;

The DBMS_UTILITY.GET_TIME program is a function from the built-in package, DBMS_UTILITY, which returns the number of hundredths of seconds that have elapsed since an arbitrary point in time. DBMS_OUTPUT is another built-in package; its PUT_LINE procedure displays output from a PL /SQL program to your screen.

Notice that there is another code element defined inside the package body besides the capture and show_elapsed procedures: the last_timing variable. This variable holds the timing value from the last call to tmr.capture. Since last_timing does not appear in the package specification, an external program (i.e., one that is not defined in this package) cannot directly reference that variable. This restriction is illustrated in the Booch diagram[2] Figure 1.1.

[2] 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.1: Booch diagram of tmr package

So if I try to access the last_timing variable from outside the tmr package, I get an error. This is shown as follows:

SQL> exec tmr.last_timing := 100;
begin tmr.last_timing := 100; end;
*
ERROR at line 1:
ORA-06550: line 1, column 14:
PLS-00302: component 'LAST_TIMING' must be declared

Why should you or anyone else care about where you define the last_timing variable? Because it illustrates a critical aspect of a package's value: integrity. If I had placed the variable in the specification, then a user of the package could write over the value of last_timing -- and completely invalidate the integrity of the package. Suppose my package specification looked like this:

PACKAGE tmr
IS
   last_timing NUMBER;
   PROCEDURE capture;
   PROCEDURE show_elapsed;
END tmr;

The package compiles and seems to work as before. But consider the following rewrite of my script to time the calc_totals procedure:

BEGIN
   tmr.capture;
   calc_totals;

   tmr.last_timing := DBMS_UTILITY.GET_TIME;

   tmr.show_elapsed;
END;
/

Since tmr.last_timing is now in the package specification, this code will compile, and completely subvert the usefulness of the tmr package. For no matter how much time calc_totals actually takes to execute, the tmr.show_elapsed procedure will always display 0 -- or very close to 0 -- hundredths of seconds for elapsed time.

If, on the other hand, I keep last_timing inside the body of the package, only the tmr.capture procedure can modify its value. A user of tmr is, therefore, guaranteed to get dependable results.

This absolute control is the reason that the package structure has been so useful to Oracle Corporation -- and one of the reasons the company has constructed dozens of built-in packages. Since you can perform only the operations and access the data structures listed in the package specification, Oracle can make technology available in a highly controlled fashion. As long as its developers write their code properly, there will never be any danger that we can disrupt Oracle Server internals by calling built-in packaged functionality.

1.3.3 Referencing Built-in Package Elements

As noted earlier, a package can have up to two parts: the specification and the body. When it comes to built-in packages, you really don't need to concern yourself with the package body. That is the implementation of the package, and something that is the responsibility of Oracle Corporation. With very few exceptions, those package bodies are "wrapped," which means that they are distributed in an encrypted format that you cannot read. This is just as well, because what you really need to do is study the specification to learn about the capabilities offered in that package.

There are two ways to use a built-in package in your own code:

1. Run a function or procedure defined in the package specification.

2. Reference a nonprogram element defined in the package specification.

Notice that you never actually execute a package itself. The package is simply a "container" for the various code elements defined in the package. Let's take a look at an example to make all this very clear. The DBMS_SQL package (examined at great length in Chapter 2) allows you to execute dynamic SQL (SQL statements constructed at runtime), a feature previously unavailable in the PL /SQL language. Here is a portion of the specification of that package:

CREATE OR REPLACE PACKAGE DBMS_SQL 
IS
  --  CONSTANTS
  --
  v6 constant integer := 0;
  native constant integer := 1;
  v7 constant integer := 2;
  --
  --  PROCEDURES AND FUNCTIONS
  --
  FUNCTION open_cursor RETURN INTEGER;

  PROCEDURE parse
     (c IN INTEGER, 
      statement IN VARCHAR2, 
      language_flag IN INTEGER);

What this tells you is that there are three different constants, one procedure, and one function defined in the package. (There is actually much, much more, of course, but this is all we need to get the point across.) To reference any of the elements, you will use the same "dot notation" used to specify columns in tables.

So if I want to open a dynamic cursor, I use this:

DECLARE
   dyncur PLS_INTEGER;
BEGIN
   dyncur := DBMS_SQL.OPEN_CURSOR;

And if I want to parse a string using the "native" database method, I would write the following code:

PROCEDURE showemps (where_in IN VARCHAR2)
IS
   dyncur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
BEGIN
   DBMS_SQL.PARSE (dyncur, 
      'SELECT ename FROM emp WHERE ' || NVL (where_in, '1=1'),
      DBMS_SQL.NATIVE);
   ...
END;

In this case, I have qualified my references to the OPEN_CURSOR, PARSE, and NATIVE elements of the DBMS_SQL package. The first two instances are programs (a function and a procedure). The third instance is a constant, passed as the third argument in my call to DBMS_SQL.PARSE.

1.3.4 Exception Handling and Built-in Packages

Programs in built-in packages can raise exceptions. You will often want to write code to check for and handle these exceptions. You should know about the different ways that exceptions can be defined and raised by programs in the built-in packages. This will affect the way you write your exception handlers.

At the beginning of each package's coverage, you will find a description of the exceptions defined within that package. Within the documentation of many of the programs within a package, you will also find an explanation of the specific exceptions that may be raised by those individual programs. When references are made to named exceptions in these explanations, they will appear in one of two forms:

PACKAGE.exception_name

or:

exception_name

If the exception name is unqualified (i.e., no package name appears before the exception name), then this exception is defined either:

• In the package currently under discussion, or

• In the STANDARD package; examples are VALUE_ERROR and NO_DATA_FOUND.

In this section, I will review the four types of exceptions you may encounter when working with built-in packages. I will then show you the kind of code you will need to write to handle exceptions properly when they propagate out from built-in packages. The following sections demonstrate how to write code to work with these different types of exceptions.

Table 1.2 summarizes these types.

1.3.5 Encapsulating Access to the Built-in Packages

You will discover (both through reading this book and through your own experience) that there are many reasons to avoid directly accessing built-in packaged functionality. In a number of cases, you will want to build your own package on top of the built-in package. This process is usually referred to as encapsulation.

Why would you bother with an encapsulation package? Any of the following reasons will do:

• The built-in packages offer lots of interesting technology, but they are not always very easy to use. You can hide the complexity, or in some cases, the poor design, and make it much easier for yourself and others to reap the benefits.

• Some of the packages contain programs that you would not want to make generally or widely available. Conversely, other programs in that same package might be very useful for the "general public." An encapsulation package can offer only those programs that should be available, while hiding the others. (In this case, you will want to revoke any EXECUTE privileges on the underlying package.)

• Write less code. Have you ever noticed the really impressive lengths of the names of Oracle built-in packages and their programs? Studies conducted by the Institute for Study Conduction estimate that by the year 2000, developers using PL /SQL will have experienced a $150 trillion loss in productivity due to having to type names like DBMS_DESCRIBE.DESCRIBE_PROCEDURE. Your encapsulations can use shorter names and thereby erase the federal deficit.

• Take full advantage of built-in packages from Oracle Developer/2000 Release 1. As you will read in the next section, there are many restrictions on accessing stored code from products like Oracle Forms. Encapsulation can help you work around these restrictions.

Roughly speaking, there are two types of encapsulation to consider when working with the built-in packages:

Extension encapsulation

This is the most common type of encapsulation for built-in packages. In this case, you provide one or more programs that extend the functionality or usability of the underlying package.

Covering encapsulation

When you create a cover for a built-in package, you create a package with a specification that matches that of the built-in package (same program names, same parameter lists). You can even give your package the same name as the built-in package, but you install it in a schema other than SYS.

When you revoke EXECUTE authority on the built-in package and grant EXECUTE authority on your package, users of the built-in package will automatically be directed to your replacement. Oracle recommends this technique, for example, with the DBMS_APPLICATION_INFO package.

1.3.6 Calling Built-in Packaged Code from Oracle Developer/2000 Release 1

If you use Oracle Developer/2000 Release 1 to build your client-side application, you can use the built-in packages, but you should be aware of the following restrictions:[3]

[3] These restrictions are not likely to affect you when you work with Oracle Developer/2000 Release 2 or above.

• You can reference only packaged procedures and functions. You cannot, for example, make reference in your client-side code to DBMS_SQL.NATIVE (a constant) or UTL_FILE.INVALID_OPERATION (an exception).

• You must supply a value for each argument in a packaged procedure or function. You cannot rely on default values in the headers of those programs. This is true even if those default values do not reference elements in the built-in packages (in other words, are literal values).

• You must be connected to the Oracle database before you can compile program units that reference built-in packages. While it is possible to work on your client-side module without connecting to Oracle, the PL /SQL compiler cannot resolve references to server-side objects like built-in packages unless you are connected.

The following sections explore these restrictions in more detail and suggest work-arounds for making full use of the built-in packages from within products like Oracle Forms and Oracle Reports.

1.3.7 Accessing Built-in Packaged Technology from Within SQL

Throughout this book, you will find documentation indicating whether a particular packaged function can be called from within an SQL statement, or whether a packaged procedure can be called by a function that, in turn, is called from within SQL. This section explains the significance of that capability.

If you are running a version of Oracle Server 7.1 and beyond, you can call PL /SQL functions from within SQL statements. (If you are not running at least Oracle Server 7.1, you can skip this section -- but you should also certainly upgrade your database software as soon as possible!) Let's take a look at an example to give you a feel for this capability.

Suppose that my formula for calculating total compensation for an employee is "salary plus commission." Here is that formula implemented in PL /SQL:

CREATE OR REPLACE FUNCTION totcomp
  (sal_in IN NUMBER, comm_in IN NUMBER)
RETURN NUMBER
IS
BEGIN
   RETURN sal_in + NVL (comm_in, 0);
END;
/

Once this program is stored in the database, I can call it from within a query as follows:

SQL> SELECT ename, totcomp (sal, comm) total_compensation FROM emp;

ENAME      TOTAL_COMPENSATION
---------- ------------------
SMITH                     800
...
MILLER                   1300

You can also call a packaged function from within a SQL statement. In this case, however, you must also provide a special statement, the RESTRICT_REFERENCES pragma, to enable that function for use inside SQL. Here, for example, is the code you would have to write to place totcomp inside a package and still call it from a query:

CREATE OR REPLACE PACKAGE empcomp
IS
   FUNCTION totcomp
     (sal_in IN NUMBER, comm_in IN NUMBER)
   RETURN NUMBER;

   PRAGMA RESTRICT_REFERENCES (total, WNDS, RNDS, WNPS, RNPS);
END;
/
CREATE OR REPLACE PACKAGE BODY empcomp
IS
   FUNCTION totcomp
     (sal_in IN NUMBER, comm_in IN NUMBER)
   RETURN NUMBER
   IS
   BEGIN
      RETURN (sal_in + NVL (comm_in, 0));
   END;
END;
/

The line in bold is the statement asserting that the empcomp.total function does not violate any of the restrictions on functions in SQL. Here is how you would call this packaged function inside SQL:

SQL> SELECT ename, empcomp.total (sal, comm) total_comp from emp;

ENAME      TOTAL_COMP
---------- ----------
SMITH             800
...
MILLER           1300

The same rules apply for built-in packaged programs callable from SQL. Oracle Corporation itself must provide a RESTRICT_REFERENCES pragma in its own package specifications for any procedure or function that is to be used from within SQL. And since Oracle did not pragmatize built-in packages prior to Oracle 7.3.3, you will not be able to call built-in packaged programs from SQL (directly or indirectly) until you install Oracle 7.3.4 or later.

If you try to call a packaged function in SQL that does not have such a pragma, you will receive this error:

SQL> SELECT utl_file.fopen ('/tmp', ename || '.dat', 'R') 
  2    FROM employee;
select utl_file.fopen ('a', 'b', 'r') from employee
       *
ERROR at line 1:
ORA-06571: Function FOPEN does not guarantee not to update database

Don't you hate those double negatives?

You will also encounter this same error if you try to execute a function in SQL that, in turn, calls a packaged procedure that does not have a pragma. For example, the DBMS_JOB.SUBMIT procedure is not "pragma-tized" for use in SQL. Consequently, the following function (exactly the same as that shown earlier, except for the addition of the procedure call) will not be executable within SQL:

CREATE OR REPLACE FUNCTION totcomp
  (sal_in IN NUMBER, comm_in IN NUMBER)
RETURN NUMBER
IS
   myjob INTEGER;
BEGIN
   DBMS_JOB.SUBMIT (myjob, 'calc_totals;');
   RETURN (sal_in + NVL (comm_in, 0));
END;
/

Here is the error I get when I try to execute my new and "improved" function:

SQL> SELECT totcomp (salary, NULL) FROM employee;
SELECT totcomp (salary, NULL) FROM employee
       *
ERROR at line 1:
ORA-06571: Function TOTCOMP does not guarantee not to update database