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.Why 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.
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)
I need to write a procedure, process_employee, that retrieves the “full name” (last name comma 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 first name space last name in all reports and messages.” Uh-oh. Now I have to scour my code for all the last-comma-first 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 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;
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 datatype 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 comma first—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 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;
Lines 3–11 implement the first fullname function, which is really nothing more than a wrapper around the last-comma-first 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, 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; /
No need to declare a local variable, 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” onscreen:
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 datatypes.
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 datatypes 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 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.
The next PL/SQL 101 article will examine working with cursors in PL/SQL, from implicit queries and explicit cursors to cursor variables.
Here is your quiz for this article:
CREATE OR REPLACE PACKAGE plch_counter IS PROCEDURE increment_by (amount_in IN PLS_INTEGER); FUNCTION current_value RETURN PLS_INTEGER; END; / CREATE OR REPLACE PACKAGE BODY plch_counter IS g_counter PLS_INTEGER := 0; PROCEDURE increment_by (amount_in IN PLS_INTEGER) IS BEGIN g_counter := g_counter + amount_in; END; FUNCTION current_value RETURN PLS_INTEGER IS BEGIN RETURN g_counter; END; END; /
Which of the following choices will display “3” after execution?
BEGIN FOR indx IN 1 .. 3 LOOP plch_counter.increment_by (1); END LOOP; DBMS_OUTPUT.put_line (plch_counter.current_value); END; /
BEGIN plch_counter.increment_by (1); END; / BEGIN plch_counter.increment_by (1); END; / BEGIN plch_counter.increment_by (1); END; / BEGIN DBMS_OUTPUT.put_line (plch_counter.current_value); END; /
BEGIN FOR indx IN 1 .. 3 LOOP plch_counter.increment_by (1); END LOOP; END; / CONNECT HR/HR SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.put_line (plch_counter.current_value); END; /
DOWNLOAD Oracle Database 11g
Photography by Scott Webb, Unsplash