Wrap Your Code in a Neat PackageBy Steven Feuerstein
Part 11 in a series of articles on understanding and using PL/SQL
Most PL/SQL-based applications consist of hundreds of thousands of lines of code—and many likely 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 code base.
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.
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.
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.
Improve 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.
Minimize 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)
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;
It would be wonderful if I could write this procedure in a way that avoids all the hard-coding and repetition: write these things once—one definition of a “full name” datatype, 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;
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;
Best of all, if I need to change the formula for fullname or expand the maximum size of the fullname_t datatype, 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;
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 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;
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; /
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; /
BEGIN timer_pkg.start_timer; FOR indx IN 1 .. 10000 LOOP NULL; END LOOP; timer_pkg.show_elapsed ('10000 Nothings'); END; /
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.
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” onscreen:
BEGIN DBMS_OUTPUT.PUT_LINE (100); END;
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’
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;
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; /
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; /
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:
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 code base 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 ORA-04068
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 PGA (Process Global Area) 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 in 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 at oracle.com/technetwork/database/features/availability/edition-based-redefinition-1-133045.pdf and docs.oracle.com/cd/E11882_01/appdev.112/e10471/adfns_editions.htm.
The next PL/SQL 101 article will examine working with cursors in PL/SQL, from implicit queries and explicit cursors to cursor variables.