1.6.1.1 Clean up a bewildering mass of standalone functions and procedures

Do you have dozens, perhaps hundreds, of standalone functions and procedures stored in the database? Do you wonder if there is overlap between these programs? Do you have a sense that you have lost control of your development/stored code environment? The package construct can help bring everything into focus and get you and your code organized.

The package is the closest thing in PL/SQL (prior to PL/SQL Release 3) to an object. A package bundles together different -- usually related -- PL/SQL elements. Rather than have separate, standalone programs to maintain the contents of a particular table, to return information from that table and so on, you can place all of those programs within the context of the package. This simple transfer of code within the boundaries of a package makes it easier for you to manage all of your code. It will also be much, much easier for others to understand and maintain the code you have written.

So if you are new to packages and really do not believe that you have a grip on what is actually out there in the database, it would be a good time to analyze your stored code and reorganize it into a set of packages.

1.6.1.2 Maintain control over your data structures

The data structures defined inside the body of packages are shared by (or accessible to) all elements of the package, but cannot be referenced outside of the package. This means that you can control tightly any access to your data. I recommend, in fact, that you never define variables and data structures in the specification of the package.

This advice applies not only to in-memory data defined inside the package, but also to table-based data in the Oracle Server. You can set up your environment so that users cannot access database tables directly for read or write purposes. You then create a package that maintains the data in the table. The owner of the package is given access to the underlying table. Finally, you grant execute authority on the package to the end users. Now all access to the table goes through the package. As a result, you can now use the PL/SQL language to apply business rules to all user-initiated transactions. This approach is particularly important when you use a third-party frontend tool like Gupta Corporation's SQL*Windows to access the Oracle database. You want to do everything you can to avoid hard-coding your data structures (in the form of SELECTs, INSERTs, and so on) into client-side code, whether in Oracle Developer/2000 or in SQL*Windows.

1.6.1.3 Need global data structures for your PL/SQL programs

When you declare a variable in the specification of a package, that variable becomes, for all intents and purposes, a global variable within a given user session. It can be accessed by any PL/SQL program, regardless of where the variable was first referenced and assigned a value, as long as that program is run under the same session. (Note that you can make use of the DBMS_PIPE package to make data available across different Oracle sessions.)

If you declare a data structure inside the body of the package, that structure is still "global" -- but only within the package. It also persists for the duration of your session; in other words, the variable (whether scalar or composite) maintains its value until it is changed or until you disconnect.

If you need to define and then access persistent, global data in your session, the PL/SQL package is the only way to accomplish this particular trick. Common scenarios requiring global data include:

• Configuration information for a session or even for an application. What is my printer name? What is the latest date allowed for entry of new orders?

• Memory-based lists of information. Rather than having to constantly go back to the database to obtain and display a list of options, you can load these values into a PL/SQL table and then display those values. You will use more memory, but improve the performance of your application.

• Running totals and other accumulated or derived values. Your application might perform lots of what-if analysis on current information in the database and new information entered by users. Package-based data structures can be used to keep track of derived, analytical values until they are ready to be saved to the database or discarded when the session ends.

1.6.1.4 Remove hard-coded literals from your application

We all know that you should never, or hardly ever, put hard-coded literal values in your application code.

One of the most common causes for program failure is, I believe, the undying belief by programmers that a particular value will never change and so can be hard-coded into a system. Why do we developers make this mistake again and again? I believe that it is a subconscious reaction to the fundamental uncertainty in our lives. Our end users change their minds about their specifications, requirements, and user interface preferences on a daily -- if not hourly -- basis. We can find no comfort, no security, from our users.

And what about our own IS management? CIOs and their architectures teams seem to always be discovering the next "silver bullet" methodology or application development tool -- and pulling the proverbial carpet out from under us.

So when you read in your application specifications that C will represent a "closed account," you are swept up by an irresistible urge to bet the house on that particular value not changing. But of course it will -- and your code will suffer for it.

You are much better off establishing a package of constants that holds all the application-wide and/or system-wide constants that you will be using. Then when you feel the need to hard-code a literal into your program, instead switch over to your constants package (I talk more about this in the "simultaneous package construction" best practice, described in the next chapter). Deposit the literal value there and give it a name with a CONSTANT declaration. From then on, reference the value by package constant name . If the value ever must be changed, you change it in the constants package specification, recompile all dependent programs, and you are done!

As an alternative, you can also define constants in the packages to which they relate. I have found that I will usually create a single, application-level package of constants if I am reviewing and cleaning up an existing application.

1.6.1.5 Isolate volatile areas of functionality

If you know that an aspect of your application -- or even the technology on which your application is based -- is volatile and bound to change, build a package around that functionality to protect your application from that volatility.

One of the most volatile areas of an application is its underlying data structures. Many developers tend to think of the database -- all those tables, primary keys, constraints and so forth -- as the bedrock of the application. Now, it is true that these data structures are the foundation upon which most code is built. But it is most definitely not true that this foundation is unchanging. The entity-relationship diagram (ERD) maps the real world to the relational world.

The real world -- at least as perceived by our users -- is a moving target. New or changed tables, modified columns, evolving relationships: these are an everyday element of our work. They are also one of the most complicated aspects of our development. It is not unusual, for instance, to require triple and quadruple joins of tables to retrieve the most basic information about an entity.

So, yes, you have to build your applications on top of these data structures, but you do not have to do so in a blind and short-sighted manner. There are two approaches you can take to deal with the complexity and volatility:

Option 1. Train all of your developers about all the subtleties of your organization's data. You will, of course, need to keep training them as your data structures change over time.

Option 2. Hide as many of these subtleties as you can, allowing your developers to work at a higher level of abstraction.

The optimal choice should be clear. While it is an admirable goal to educate everyone about every aspect of the work, it is simply not practical. You can use a package to encapsulate the triple joins and present a unified front to a developer. They can skip the complex SQL and apply the prebuilt package code to their needs much more quickly. Assume, in other words, that your structures will change. Protect your code (and your sanity) accordingly. Here are some recommendations to follow that employ the package to improve the robustness of your application:

1. Avoid implicit queries. When you write an implicit query, you place a SELECT statement directly in your program. You fetch data from the database directly into local PL/SQL variables. When you take this approach, you essentially hard-code your data structures into your programs. What happens when that two-table join turns into a six-table join? You have to find all those implicit queries and fix them.

2. Set as a general rule that developers do not write DML statements directly in their own applications. Again, if everyone is writing whatever SQL they find appropriate to their individual circumstances, you will end up with an application in which your entity relationships are distributed throughout your code. How do you maintain and upgrade such code to match your changing database? Instead...

3. Consolidate all of your SQL statements into one or more packages. Provide programmatic access to the SQL , or offer explicit cursors defined in the package. With this approach, you anticipate developer needs concerning the SQL layer. Will developers need to update the emp table with a new salary? Provide a procedure that does this. Do you need to query employees by decreasing salaries? Create an explicit cursor with this structure and let developers open and fetch from the cursor.

If you succeed in predefining and consolidating your SQL statements behind the package interface, you will have gone a long way towards making your application change-proof. This approach takes up-front planning and lots of discipline. You might even want to build scripts to query the contents of the USER_SOURCE data dictionary view to verify that DML statements do not appear outside of your SQL packages. In the long run, however, you will achieve higher productivity and higher code quality. Individual developers are liberated from writing complex, bug-prone SQL and instead concentrate on the user interface -- or whatever is the task at hand.

1.6.1.6 Hide weaknesses to facilitate upgrades and fixes.

Every release of every piece of software comes with bugs, undocumented "features," and functionality that lacks, shall we say, a certain polish. You can whine about this situation, but sooner or later you have to deal with it. This usually means that you have to use elements of the language that are substandard. You will figure out the workaround or whatever compensation is necessary to keep the development process moving forward. At this point, you have two choices of how to apply this workaround:

1. Whenever you encounter the problem area, you code the workaround directly in the program.

2. You build a package that contains the workaround. The package specification provides the solution without revealing the nature of the workaround. It is hidden inside the body of the package.

In the first approach, which is almost always the route chosen, you hard-code the (usually temporary) drawback of the language directly in multiple places in your programs. When the upgrade to PL/SQL (or whatever language you are using) arrives at your installation, you have to hunt down every place you put the workaround and replace it with the new, fixed functionality.

With the second (package-based) approach, the code for the workaround is in one place only. When the patch tape or upgrade arrives, you make the change only inside the body of the package. All the code that called the package element to apply the workaround will now call that same package element, but this time use the new, fixed version.

If you build yourself a layer of code with a package that hides the implementation of workarounds, you can then easily and rapidly apply upgrades that completely obviate the need for the workaround.

1.6.1.7 An example

To illustrate this technique, consider the task of clearing or emptying PL/SQL tables. Prior to Release 2.3 of PL/SQL , the only way to delete all rows from a PL/SQL table was to assign an empty table to the populated table. There is, in other words, no DELETE function or operator for PL/SQL tables. The PLVtab package of PL/Vision makes it as easy as possible for you to use PL/SQL tables by predefined table types in the package. To similarly ease the task of emptying tables, PLVtab provides an empty table for each table TYPE defined in the package. The PLVprsps.init_table procedure below shows how these elements are put to use in PL/Vision to delete all rows from a PL/SQL table:

PROCEDURE init_table
   (tokens_out IN OUT PLVtab.vc2000_table,
    num_tokens_out IN OUT INTEGER)
IS
BEGIN
   tokens_out := PLVtab.empty_vc2000;
   num_tokens_out := 0;
END;

The problem with this approach is that it exposes the implementation of my workaround and makes it very difficult to upgrade this code to PL/SQL Release 2.3.

You see, PL/SQL Release 2.3 provides a DELETE method for PL/SQL tables. Rather than assigning an empty table to delete all rows from tokens_out , I could simply issue this statement:

tokens_out.DELETE;

I can certainly make this substitution in the init_table program, but in how many other places are these empty tables utilized? Taking a different approach within PLVtab would have left me in a much stronger position. Suppose that instead of providing a set of predefined empty tables, I built a series of procedures to empty the tables. The procedure to empty the VARCHAR2(2000) tables would look like this:

PROCEDURE empty (table_inout IN OUT PLVtab.vc2000_table) IS
BEGIN
   tokens_inout := PLVtab.empty_vc2000;
END;

and the init_table procedure would change to this:

PROCEDURE init_table
   (tokens_out IN OUT PLVtab.vc2000_table,
    num_tokens_out IN OUT INTEGER)
IS
BEGIN
   PLVtab.empty (tokens_out);
   num_tokens_out := 0;
END;

Notice that there is no mention of the empty table outside of PLVtab. So when Release 2.3 comes along, I can change the empty procedure of PLVtab as follows:

PROCEDURE empty (table_inout IN OUT PLVtab.vc2000_table) IS

BEGIN
   tokens_inout.DELETE;
END;

Like magic, without making any change to the PLVprsps.init_table procedure, it is now using the new capabilities of the PL/SQL language. This is just one very simple example of how you can and should use packages to make feasible the upgrade to new features and fixes to bugs.

There are many situations in PL/SQL development that cry out for a package. I hope the previous sections will help raise a flag as you proceed through your application development projects. Do you find yourself dealing repeatedly with some weakness in PL/SQL or another element of the Oracle product set? Are you writing the same complex SQL statement again and again in your code? Stop! Take the time required to bundle the logic, the flaws, the relationships into a package. The payoff will come instantaneously and continuously; the investment will never be regretted.

Once you have decided to build your package and constructed the interface (specification) for that package, you will then embark on constructing the guts of the package: its body.

1.6.2.1 Package body syntax

The general syntax for the package body is shown below:

PACKAGE BODY package_name 
IS
   [ declarations of variables and types ]
   [ header and SELECT statement of cursors ]
   [ header and body of modules ]
[ BEGIN 
     executable statements ]
[ EXCEPTION
      exception handlers ]
END [ package_name ];

In the body you can declare other variables, but you do not repeat the declarations in the specification. The body contains the full implementation of cursors and modules. In the case of a cursor, the package body contains both the header and the SQL statement for the cursor. In the case of a module, the package body contains both the header and body of the module.

The BEGIN keyword indicates the presence of an execution or initialization section for the package. This section can also optionally include an exception section.

As with a procedure, function, and package specification, you can add the name of the package, as a label, after the END keyword in both the specification and package.

1.6.3.1 When to use the initialization section

Use the initialization section only when you need to set the initial values of package elements using rules and complex logic that cannot be handled in the default value syntax for variables. You do not, for example, need an initialization section to set the value of the constant earliest_date to today's date. Instead, simply declare the variable with a default value.

The following package body contains unnecessary initialization code:

PACKAGE config 
IS
   earliest_date DATE;
BEGIN   
   earliest_date := SYSDATE;
END config;

This code should be replaced with a much simpler and more direct default assignment as follows:

PACKAGE config 
IS
   earliest_date DATE := SYSDATE;
END config;

Suppose, on the other hand, that you wanted to use PLVlst to maintain a list of companies that the user has selected for financial analysis. The analysis is performed by a package you built called compcalc (COMPany CALCulation). You want to make sure that the list is defined and available whenever any program in compcalc is used, but you don't want the list refreshed or created anytime after the start of a user session. This is the perfect opportunity for an initialization section. The following package shows you how to achieve this effect:

PACKAGE BODY compcalc
IS
   c_list CHAR(8) := 'compcalc';

   PROCEDURE total_sales 
   IS
   BEGIN
      FOR list_ind IN 1 .. PLVlst.nitems (c_list)
      LOOP
         calc_sales (PLVlst.getitem (c_list, list_ind));
      END LOOP; 
   END;

BEGIN
   PLVlst.make (c_list);
END;

The package body declares a constant containing the name of the list. This constant is then referenced throughout the package to avoid hard-coding of literals. The total_sales procedure calculates the sales for each item in the list. The initialization section at the bottom of the package consists of a single line of code that makes the list for use in the package. The PLVlst.make procedure is called only once in a user's session; the list is then available for the duration of the session.

You will also want to use an initialization section when you need to handle exceptions that might arise when initializing values.

1.6.3.2 The exception section

As noted earlier, the initialization section of a package can also have its own exception section. The ability to handle exceptions is one of the most important characteristics of this section of the package.

Remember that the exception handlers in this section will only trap exceptions that occur in the initialization section itself. If an exception is raised in one of the programs defined in the package or if an exception is raised in the process of instantiating data in the package, the initialization section exception handlers will not be able to trap those exceptions.

To understand this flow, consider the following package specification and body:

PACKAGE going
IS
   PROCEDURE bad;
END going;

PACKAGE BODY going
IS
   fast VARCHAR2(3) := 'fast';

   PROCEDURE bad IS 
   BEGIN
      DBMS_OUTPUT.PUT_LINE (fast);
   END;
BEGIN
   NULL;
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.PUT_LINE ('too late');
END going;

This package contains a single public procedure, going.bad , and a single private variable, fast . When any program tries to execute going.bad , the PL/SQL runtime engine loads the package into shared memory and instantiates all package data. When it tries to assign the value of fast to the variable going.bad , however, the runtime engine will raise the VALUE_ERROR exception. The literal value has four characters, but the fast variable is restricted to three characters.

It would appear at first glance that the exception handler for VALUE_ERROR at the bottom of the package body would trap this exception and display "too late". Instead, the exception will go unhandled.

Let's now see how to use the initialization section to your advantage when initializing values in a package. Consider the simple package body below:

PACKAGE BODY analysis
IS
   best_salesperson INTEGER := sales_list (1);
END analysis;

This package sets the default value for the best salesperson ID to the value in row 1 of the sales_list PL/SQL table. If for any reason this row has not yet been defined, the PL/SQL runtime engine will raise a NO_DATA_FOUND exception simply for trying to reference the analysis.best_salesperson variable.

If you move the assignment to an initialization section, on the other hand, you can handle gracefully the scenario in which row 1 in the table has not been set:

PACKAGE BODY analysis
IS
   best_salesperson INTEGER;
BEGIN
   best_salesperson := sales_list (1);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      best_salesperson := NULL;
END analysis;

1.6.5.1 RESTRICT_REFERENCES pragma

A stored function can exist as a standalone function or as a function in a package. For standalone functions, the Oracle Server automatically determines whether it is callable in SQL . It will, for example, reject your SQL statement if it uses a function that issues an UPDATE statement. The situation with packaged functions is a bit more complicated.

As noted earlier, the specification and body of a package are distinct; a specification can exist even before its body. For this and other reasons, the Oracle Server cannot automatically determine (when you execute your SQL ) that a packaged function is valid for SQL execution. Instead, you must state explicitly the "purity level" of a function in a package with the RESTRICT_REFERENCES pragma. The Oracle Server then determines at compile time (of the package body) if the function violates the purity level, and raise a compilation error if this is the case. Once the package is compiled, the functions for which assertions have been made can be called in SQL .

Let's explore the specific syntax required to achieve this effect.

A pragma is a special directive to the PL/SQL compiler. If you have ever created a programmer-defined, named exception, you have already encountered your first pragma. In the case of the RESTRICT_REFERENCES pragma, you are telling the compiler the purity level you believe your function meets or exceeds.

You need a separate PRAGMA statement for each packaged function you wish to use in a SQL statement, and it must come after the function declaration in the package specification (you do not specify the pragma in the package body).

To assert a purity level with the pragma, use the following syntax:

PRAGMA RESTRICT_REFERENCES 
   (function_name, WNDS [, WNPS] [, RNDS] [, RNPS])

where function_name is the name of the function whose purity level you wish to assert, and the four different codes have the following meanings:

Notice that only the WNDS level is mandatory in the pragma. That is consistent with the restriction that stored functions in SQL may not execute an UPDATE, INSERT, or DELETE statement. All other states are optional. You can list them in any order, but you must include the WNDS argument. No one argument implies another argument. For example, I can write to the database without reading from it. I can read a package variable without writing to a package variable.

Here is an example of two different purity level assertions for functions in the company_financials package:

PACKAGE company_financials
IS
   FUNCTION company_type (type_code_in IN VARCHAR2) 
      RETURN VARCHAR2;

   FUNCTION company_name (company_id_in IN company.company_id%TYPE) 
      RETURN VARCHAR2;

   PRAGMA RESTRICT_REFERENCES (company_type, WNDS, RNDS, WNPS, RNPS);
   PRAGMA RESTRICT_REFERENCES (company_name, WNDS, WNPS, RNPS);
END company;

In this package, the company_name function does read from the database to obtain the name for the specified company. Notice that I placed both pragmas together at the bottom of the package specification. The pragma does not need to immediately follow the function specification. I also went to the trouble of specifying the WNPS and RNPS arguments for both of the functions. Oracle Corporation recommends that you assert the highest possible purity levels so that the compiler will never reject the function unnecessarily.

I have found, on the other hand, that the PL/SQL compiler does at times reject my purity level assertions when there does not seem to be any apparent violation. You may at times have to retreat to the minimal WNDS assertion simply to get your package to compile.

1.6.5.2 Asserting the purity level of the initialization section

If your package contains an initialization section (executable statements after a BEGIN statement in the package body), you must also assert the purity level of that section. The initialization section is executed automatically the first time any package object is referenced. So if a packaged function is used in a SQL statement, it will trigger execution of that code. If the initialization section modifies package variables or database information, the compiler needs to know about that through the pragma.

You can assert the purity level of the initialization section either directly or indirectly. To use a direct assertion, you use this variation of the pragma RESTRICT_REFERENCES:

PRAGMA RESTRICT_REFERENCES 
   (package_name, WNDS, [, WNPS] [, RNDS] [, RNPS])

Instead of specifying the name of the function, you include the name of the package itself, followed by all the applicable state arguments. In the following argument I assert only WNDS and WNPS because the initialization section reads data from the configuration table and also reads the value of a global variable from another package ( session_pkg.user_id ).

PACKAGE configure
IS
   PRAGMA RESTRICT_REFERENCES (configure, WNDS, WNPS);
   user_name VARCHAR2(100);
END configure;

PACKAGE BODY configure
IS
BEGIN
   SELECT lname || ', ' || fname INTO user_name
     FROM user_table
    WHERE user_id = session_pkg.user_id;
END configure;

Why can I assert the WNPS even though I do write to the user_name package variable? Answer: It's a variable from this same package, so the action is not considered a side effect.

You can also assert the purity level of the package's initialization section by allowing the compiler to infer that level from the purity level(s) of all the pragmas for individual functions in the package. In the following version of the company package, the two pragmas for the functions allow the Oracle Server to infer a combined purity level of RNDS, WNPS for the initialization section. This means that the initialization section cannot read from the database and cannot write to a package variable.

PACKAGE company
IS
   FUNCTION get_company (company_id_in IN VARCHAR2) 
      RETURN company%ROWTYPE;

   FUNCTION deactivate_company (company_id_in IN company.company_id%TYPE)
      RETURN VARCHAR2;

   PRAGMA RESTRICT_REFERENCES (get_company, RNDS, WNPS);
   PRAGMA RESTRICT_REFERENCES (deactivate_name, WNPS);
END company;

Generally, you are probably better off providing an explicit purity level assertion for the initialization section. This makes it easier for those responsible for maintaining the package to understand both your intentions and your understanding of the package.