Wrap your PL/SQL code in a nice neat package

Part 11 in a series of articles on understanding and using PL/SQL for accessing Oracle Database

PL/SQL is one of the core technologies at Oracle and is essential to leveraging the full potential of Oracle Database. PL/SQL combines the relational data access capabilities of the Structured Query Language with a flexible embedded procedural language, and it executes complex queries and programmatic logic run inside the database engine itself. This enhances the agility, efficiency, and performance of database-driven applications.

Steven Feuerstein, one of the industry’s best-respected and most prolific experts in PL/SQL, wrote a 12-part tutorial series on the language. Those articles, first published in 2011, have been among the most popular ever published on the Oracle website and continue to find new readers and enthusiasts in the database community. Beginning with the first installment, the entire series is being updated and republished; please enjoy!

Most PL/SQL-based applications are built from hundreds of thousands of lines of code—and some run into the millions—that meet detailed, ever-changing user requirements. Business logic is implemented primarily in procedures and functions, but PL/SQL developers need to decide where to put those procedures and functions. In this article, I explore how to use packages to organize and expose an application’s functionality in ways that make it easier to build, maintain, and optimize the codebase.

A package is a grouping or packaging of elements of PL/SQL code into a named program unit stored in the database. Packages provide a structure (both logically and physically) in which you can organize your programs and other PL/SQL elements such as cursors, TYPEs, and variables. They also offer significant functionality, including the ability to hide logic and data from view and to define and manipulate “global” or session-persistent data.

A package always has a specification, which defines the package items that can be referenced from outside the package. Most packages also have a body, which implements any subprograms (procedures and functions) in the package. The body can also contain private items, such as package-level variables and types, which can be referenced only from within the package. A package body can also have an initialization section, which developers use to implement complex setup steps and validation for a package.

Why use packages?

The package is a powerful and important element of the PL/SQL language. It should, in fact, be the fundamental building block of any PL/SQL-based application. What makes the package so powerful and important? Consider its advantages:

• Group related functionality. One of the most fundamental benefits of a package is that it can group related functionality and then make that functionality available through an API. Rather than having to look through hundreds of thousands of separate procedures and functions, you find the package that contains the functionality you need (which should be easily identifiable by its name) and then drill down to the subprograms you need within it. And when you need to add new programs to your application, the package offers a “home” for that functionality. Without packages, developers have a much harder time understanding and maintaining their code.
• Ability to hide implementation details. With a package, you choose what to put into the specification for everyone to see and use and what to put into the body, which is visible only to the owner of the package. This ability to hide implementation details has two critical benefits. First, you can change the implementation without changing the specification. This means that all the programs that call procedures and functions in the package do not have to change. This feature greatly increases the flexibility you have for enhancing your code while limiting the impact on the application. Second, you can hide implementation details you don’t want users of the package to be aware of.
• Improved performance. The first time you invoke an element in a package, Oracle Database loads the entire package into memory. Subsequent references to other elements in the same package require no additional disk I/O. Also, package-level variables can be used as a session-level cache, offering a variety of ways to reduce the time it takes to access data.
• Minimized program unit recompilation. External programs (not defined in the package) can call only subprograms listed in the specification. If you change and recompile the package body, those external programs will not be invalidated. Minimizing the need to recompile code is a critical factor in administering large bodies of application logic.

There are many reasons to use packages, but first you need to know how to build them and take advantage of their unique features. In this article, I start with a simple package that demonstrates the power of this application building block. Then I explore two key features—package-level data and overloading—in more detail.

A simple package example

Suppose that my employees table is defined as follows:

SQL> desc employees

Name             Type
————————————     —————————————
EMPLOYEE_ID      NUMBER(38)
FIRST_NAME       VARCHAR2(30)
LAST_NAME        VARCHAR2(50)

I need to write a procedure, process_employee, that retrieves the “full name” (the last name, followed by a comma, followed by the first name) of an employee and then performs other tasks. That’s easy enough to do in PL/SQL, as shown in Listing 1.

Code listing 1: The process_employee procedure

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

Although this code may work fine today, it contains several “time bombs” that could cause problems in months or years to come, including the following:

• The length of the l_fullname variable is fixed at 100. I would have preferred to use %TYPE to declare the variable, but the full name is a derived value and there is no column with which I can attach %TYPE. So I just figured that 100 would be “big enough.” And it will be—until the DBA increases the maximum length of either of these columns.
• The formula that produces a “full name” has been placed right inside this procedure. And there’s a good chance that the same concatenation appears over and over again in my code. Why not? It’s such a simple formula! Ah, but what do I do when the users call me with some news: “We’ve changed our minds. We want to see the names as the first name, followed by a space, followed by the last name in all reports and messages.” Uh oh. Now I have to scour my code for all the “last name, comma, first name” constructions.
• Finally, because it is so easy to write SQL inside PL/SQL programs, the query itself will likely appear in many places in my application. Repeating SQL statements can make it very hard to optimize the performance of my code and keep up with changing user requirements.

It would be wonderful if I could write this procedure in a way that avoids all the hardcoding and repetition by writing these things once—one definition of a “full name” data type, one representation of the formula, and one version of the query—and then call them wherever they are needed. A package offers the perfect repository for such single-source implementations.

Consider the package specification in Listing 2.

Code listing 2: The employee_pkg specification

1    CREATE OR REPLACE PACKAGE employee_pkg
2    AS
3        SUBTYPE fullname_t IS VARCHAR2 (100);
4    
5        FUNCTION fullname (
6           last_in  employees.last_name%TYPE,
7           first_in  employees.first_name%TYPE)
8           RETURN fullname_t;
9    
10        FUNCTION fullname (
11           employee_id_in IN employees.employee_id%TYPE)
12           RETURN fullname_t;
13    END employee_pkg;

The employee_pkg specification essentially lists the different code elements that will be available for use outside the package. In line 3, I declare my own data type called fullname_t that is a subtype of the VARCHAR2 type. In lines 5–8, I define a function called fullname that accepts a last name and a first name and returns the full name. (Note that the formula itself—last name, comma, first name—is not visible in the package specification. That’s a good thing, as you will soon see.) In lines 10–12, I declare a second function, also called fullname. This version accepts a primary key for an employee and returns the full name for that employee. Having two functions with the same name is an example of overloading, which I explore in more detail below.

Even though I have not yet implemented the two functions in this package specification, I can now revisit the process_employee procedure and see how I can use these packaged elements, which are referenced in the form package_name.element_name. As shown in Listing 3, I use the subtype to declare the l_name variable, and I call the function instead of writing a query directly inside the function.

Code listing 3: Block calling the employee_pkg package

CREATE OR REPLACE PROCEDURE process_employee (
   employee_id_in IN employees.employee_id%TYPE)
IS
   l_name employee_pkg.fullname_t;
   employee_id_in   employees.employee_id%TYPE := 1;
BEGIN
   l_name := employee_pkg.fullname (employee_id_in);
   ...
END;

As a result, the code implementing the formula and the SQL query have been moved out of my procedure; the resulting code is cleaner and simpler. The same will be true of all the other programs that need to work with the full name of an employee.

Best of all, if I need to change the formula for fullname or expand the maximum size of the fullname_t data type, I will simply make the appropriate change to the package and recompile it. But all the programs that use the packaged elements will not need to change. Listing 4 is the implementation of the body of employee_pkg.

Code listing 4: The employee_pkg package body

1    CREATE OR REPLACE PACKAGE BODY employee_pkg
2    AS
3       FUNCTION fullname (
4          last_in employees.last_name%TYPE,
5          first_in employees.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         l_fullname fullname_t;
17      BEGIN
18         SELECT fullname (last_name, first_name) INTO l_fullname
19           FROM employees
20          WHERE employee_id = employee_id_in;
21    
22         RETURN l_fullname;
23       END;
24    END employee_pkg;

Lines 3–11 implement the first fullname function, which is really nothing more than a wrapper around the last name-comma-first name formula. Lines 13–23 implement the second fullname function, which gets the first and last name for the specified employee and combines them by calling the first fullname function.

So what happens when my users call and say, “First-space-last, please!”? I will not groan and work late into the night, hunting down occurrences of “|| ', ' ||” in my code. Instead, I will change the implementation of my employee_pkg.fullname function in about five seconds flat, recompile the package body, and astound my users by announcing that they are ready to go.

And what happens when I upgrade to Oracle Database 11g and discover the amazing PL/SQL function result cache? I simply add the RESULT_CACHE clause to the function headers, recompile the package, and then enjoy the greatly reduced CPU cycles needed to repeatedly retrieve the full name for the same employee.

Ah, the power of hiding implementation details in packages!

Package-level data

Package-level 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 following package specification, for example, declares one variable and one constant at the package level:

CREATE OR REPLACE PACKAGE plsql_limits
IS
   c_varchar2_length CONSTANT 
      PLS_INTEGER := 32767;
   g_start_time PLS_INTEGER;
END;

When you declare a variable within a subprogram or a block, it is a local variable. Its scope is restricted to that block, and when the block terminates, the memory for that variable is released.

The scope of package data, however, is not an individual program or block, 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 of a particular program).

If package data is declared inside the package body, that data will persist for the session but can be accessed only by elements defined in the package itself (private data). If, on the other hand, package data is declared inside the package specification, that data will persist for the session and will be directly accessible (for both reading and modifying) by any program that has EXECUTE authority on that package (public data).

Let’s take a look at how package-level data can be of use to you. The DBMS_UTILITY package offers a function named GET_CPU_TIME that can be used to calculate the elapsed time of your code with hundredth-of-a-second precision. The anonymous block in Listing 5 demonstrates how to use this function to determine how long it takes to do nothing 10,000 times.

Code listing 5: DBMS_UTILITY.GET_CPU_TIME measures

DECLARE
   l_start   PLS_INTEGER;
BEGIN
   /* Get and save the starting time. */
   l_start := DBMS_UTILITY.get_cpu_time;

   /* Run your code. */
   FOR indx IN 1 .. 10000
   LOOP
      NULL;
   END LOOP;

   /* Subtract starting time from current time. */
   DBMS_OUTPUT.put_line (
      DBMS_UTILITY.get_cpu_time - l_start);
END;
/

That’s simple enough, but it also means that a developer who wants to take advantage of this utility has to declare a local variable to hold the starting time, remember and write the formula (end minus start), and then display the value.

Surely most developers have better ways to spend their time. Perhaps a package could help. Consider, for example, the timer package (timer_pkg) in Listing 6.

Code listing 6: The timer_pkg package

CREATE OR REPLACE PACKAGE timer_pkg
IS
   PROCEDURE start_timer;

   PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL);
END timer_pkg;
/

CREATE OR REPLACE PACKAGE BODY timer_pkg
IS
   g_start_time   NUMBER := NULL;

   PROCEDURE start_timer
   IS
   BEGIN
      g_start_time := DBMS_UTILITY.get_cpu_time;
   END;
   PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
            message_in
         || ': '
         || TO_CHAR (DBMS_UTILITY.get_cpu_time - g_start_time));

      start_timer;
   END;
END timer_pkg;
/

Before I explain the timer_pkg implementation, let’s look at how the package makes it easier for developers to use the GET_CPU_TIME utility. My previous anonymous block, back in Listing 5, now looks like this:

BEGIN
   timer_pkg.start_timer;
   FOR indx IN 1 .. 10000
   LOOP
      NULL;
   END LOOP;
   timer_pkg.show_elapsed ('10000 Nothings');
END;
/

There’s no need to declare a local variable and no need to understand the way the GET_CPU_TIME function works (or even that it exists). Just call the appropriate subprograms and see how well your code is performing. And the timer_pkg package makes it easy to display a message with the timing and automatically reset the start time after showing the elapsed time.

The timer_pkg package relies on a single package-level variable, g_start_time, to remember the starting time between calls to subprograms in the package. This is possible only because of the session-level persistence of package data.

This timer package is also a nice example of an API, a set of subprograms that offers an interface to underlying functionality. The package specification lists the tasks you can perform, and the package body hides the way the timer is implemented, taking advantage, in this case, of the GET_CPU_TIME function. If someday Oracle comes up with a better mechanism for doing this kind of elapsed time analysis, I will have to change only the package body and all of my users will be able to instantly take advantage of the new and improved technique.

Subprogram overloading

Overloading occurs when you have two or more subprograms with the same name in a program unit. You can overload subprograms in any declaration section; that is, this feature is not available only in packages. You will, however, find that almost all overloading occurs inside packages. And that’s because overloading is most helpful in APIs.

Overloading can greatly simplify your life and the lives of other developers. When building an API, developers often find that they need to execute the same kind of logic but for different types of data or various scenarios that require different inputs. Without overloading, you would have to come up with a different name for each variation and users of your code would need to remember which is which.

With overloading, the users of your package need to know only the one name that describes the functionality needed. The compiler then does the rest of the work: It analyzes the arguments passed to the subprogram and matches them up with an overloading.

Let’s take a look at how you can use overloading to make it easier to display information onscreen from within a PL/SQL block.

Oracle Database includes the DBMS_OUTPUT.PUT_LINE procedure to send text to system output. This procedure is not overloaded. There is a single implementation accepting a single VARCHAR2 value.

So if you pass an expression to DBMS_OUTPUT.PUT_LINE that evaluates to a VARCHAR2 value, it will work just fine. Because numbers, for example, are implicitly converted to a string, the following statement will result in the display of “100”:

BEGIN
   DBMS_OUTPUT.PUT_LINE (100);
END;

If, on the other hand, you try to display a Boolean expression, you will get an error message:

SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE (TRUE);
  3  END;
  4  /
   DBMS_OUTPUT.PUT_LINE (TRUE);
   *
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00306: wrong number or types of 
arguments in call to 'PUT_LINE'

This happens because PL/SQL does not support implicit conversions from Boolean to VARCHAR2 data types.

As a result, many developers write code like this when they want to display a Boolean:

IF l_student_is_registered
THEN
   DBMS_OUTPUT.PUT_LINE ('TRUE');
ELSE
   DBMS_OUTPUT.PUT_LINE ('FALSE');
END IF;

Now there’s a recipe for reducing your productivity and introducing bloat to your code. Overloading to the rescue!

I would like to make it easy to display Boolean values and date values that include the time stamp (an implicit conversion of a date to a string using the default format mask ignores the time).

Without overloading, I need to come up with different procedure names for each type of data, as shown in Listing 7.

Code listing 7: The my_output package without overloading

CREATE OR REPLACE PACKAGE my_output
IS
   PROCEDURE put_string (value_in IN VARCHAR2);

   PROCEDURE put_boolean (value_in IN BOOLEAN);

   PROCEDURE put_date (
      value_in   IN DATE,
      mask_in    IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS');
END my_output;
/

It’s not so hard to come up with the procedure names, but it is much more effort for a user of this functionality to remember or look up the correct name. Is the procedure for displaying a date named “put_date” or “put_datetime” or maybe “put_timestamp”?

With overloading, however, my package specification procedures are easy to remember or look up, as shown in Listing 8.

Code listing 8: The my_output package with overloading

CREATE OR REPLACE PACKAGE my_output
IS
   PROCEDURE put_line (value_in IN VARCHAR2);

   PROCEDURE put_line (value_in IN BOOLEAN);

   PROCEDURE put_line (
      value_in   IN DATE,
      mask_in    IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS');
END my_output;
/

The listing includes three procedures, all with the same name, because they all do the same thing: display data. All I, as a user, have to remember is that I call a procedure to “put/display a line of data.” I pass it the data I want to display, and the PL/SQL compiler automatically figures out which of the overloadings is the right one.

I like it when smart software takes care of the “small details” so I can stay focused on implementing user requirements.

There are many rules and restrictions for overloading, which are beyond the scope of this article. Here are a few key points to keep in mind as you start building your own packages:

• Overloadings almost always involve subprograms with different numbers and/or types of parameters, as you saw in the my_output package in Listing 8.
• You can overload a function and a procedure with the same parameter list.
• Make sure the data types of the parameters across overloadings are not too similar; you cannot, for example, overload two procedures whose only difference is that one uses a CHAR parameter whereas the other uses a VARCHAR2 parameter.

When you build overloaded modules, you spend more time in design and implementation than you might with separate, standalone modules. This additional up-front time will be repaid handsomely down the line, because you and others will find it much easier to use your programs.

The building blocks of applications

It is possible to build applications in PL/SQL without using packages. You could, instead, create thousands of schema-level functions and procedures. If you take this path, however, I guarantee that you will come to regret it. You will soon discover that it is difficult to find code and even more challenging to change existing code in your applications.

I recommend that, instead, you always put your procedures and functions inside packages and move as much of the code in triggers and object types into packages as possible. You will then find your sprawling codebase easier to manage, whether it is a matter of analyzing the impact of a proposed change or applying a fix with minimum impact on existing code.

Take full advantage of overloading to simplify the API you present to the users of the package. Explore ways you can use package-level data.

Package state and the ORA-04068 error

When a package has at least one constant or variable declared at the package level, that package is said to have state or to be stateful. When a session uses a stateful package, Oracle Database uses session-specific memory to store that session’s values for all package-level data.

If a stateful package is recompiled, all sessions that have used the package will fail with the ORA-04068 error the next time they try to access the package. That’s because the state of the package in each session is now out of date, so the package must be reinitialized.

Furthermore, once the ORA-04068 error has been raised, the state of all packages in the session—including, for example, DBMS_OUTPUT—is uninitialized. This usually means that the user must disconnect from the session and reconnect in order to use the application successfully.

The potential for ORA-04068 errors has meant that when IT departments must upgrade the code in their application, they need to first have all users log out from the application. In the 24/7 world of the internet, however, this is not something many companies can afford to do.

So starting with Oracle Database 11g Release 2, Oracle offers the Edition-Based Redefinition feature. With this feature, organizations can now “hot patch” their applications, even if doing so involves changes to table structures as well as changes to code. Users no longer need to disconnect while upgrades are taking place.

Find out more about Edition-Based Redefinition.

The final PL/SQL 101 tutorial will examine working with cursors in PL/SQL, from implicit queries and explicit cursors to cursor variables.

Dig deeper

• See the rest of the series.

Illustration: Wes Rowell