16.5.1 Architecture of Package-Based Data

The first time you reference a package element, the entire package (compiled) is loaded into the SGA of the Oracle database instance on the server. That code is then shared by all sessions having EXECUTE authority on the package.

The data that is declared by the package elements is also instantiated in the SGA, but it is not shared across all sessions. Instead, each Oracle session is assigned its own private PL/SQL area, which contains a copy of the package data (see Figure 16.4 ). This private PL/SQL area is maintained in the SGA for as long as your session is running. The values assigned to your packaged data structures also remain available in the SGA throughout your session. In other words, they persist for the duration.

Contrast this behavior with the variables instantiated in the declaration section of a standalone module. The scope of those variables is restricted to the module. When the module terminates, the memory and values associated with those variables are released. They are no more.

Figure 16.4: Private PL/SQL areas in shared memory

The scope of a package is, however, the entire schema in which it is defined. Any session that has EXECUTE authority on the package may access the package and use the data defined inside the package. Because the scope of the package data is the entire session, their values are maintained in the SGA. You are not, in other words, simply sharing the data structure among your programs. You share the values in those structures as well.

16.5.2 Global Within a Single Oracle Session

As a result of the SGA-based architecture, package data structures act as globals within the PL/SQL environment. Remember, however, that they are globals only within a single Oracle session or connection. Package data is not shared across sessions. If you need to share data between different Oracle sessions, you must use the DBMS_PIPE package (see Appendix C , for more information).

You need to be careful about assuming that different parts of your application do maintain a single Oracle connection. There are times when a tool may establish a new connection to the database to perform an action. If this occurs, the data you have stored in a package in the first connection will not be available.

Consider the scenario in Figure 16.5 . An Oracle Forms application has saved values to data structures in a package. When the form calls a stored procedure, this stored procedure can access these same package-based variables and values as the form, because they share a single Oracle connection.

The form then uses the RUN_PRODUCT built-in to kick off a report using Oracle Reports. By default, Oracle Reports uses a second connection to the database (same user name and password) to run the report. So even if this report accesses the same package and its data structures, the values in those data structures will not match those used by the form. It is a different Oracle connection and a new instantiation of the data structures.

Figure 16.5: Two Oracle connections between Oracle Forms and Oracle Reports

Just as there are two types of data structures in the package (public and private), there are also two types of global package data to consider: global public data and global private data. The next two sections explore the differences between these kinds of package data.

16.5.3 Global Public Data

Any data structure declared in the specification of a package is a global, public data structure. This means that any program outside of the package can access the data structure. You can, for example, define a PL/SQL table in a package specification and use it to keep a running list of all employees selected for a raise. You can also create a package of constants which are used throughout all of your programs. Then all developers will reference the packaged constants instead of hardcoding the values in their programs.

You can also change global public data structures, unless they are variables declared as CONSTANTs in the declaration statement.

Global data is the proverbial "loose cannon" of programming. It is very convenient to declare, and have available from any module at any point in time, all sorts of information. Reliance on global data structures, however, leads to unstructured code that is full of side effects.

Remember that the specification of a module should give you all the information you need to understand how to call and use that module. If the program reads and/or writes global data structures, you cannot tell this from the module specification. You cannot be sure of what is happening in your application and which program changes what data.

It is always preferable to pass data as parameters in and out of modules. In that way, the reliance on those data structures is documented in the specification and can be accounted for by the developer.

On the other hand, you should create named, global data structures for information that truly is global to an application, such as constants and configuration information.

16.5.4 Global Private Data

A global but private data structure, also called package-level data, is declared in the body of the package. Since it does not appear in the specification, this data cannot be referenced outside of the package -- only from within the package, by other package elements. This data only exists on the level of the package.

A data structure declared in a package still does function as a global. Since it is not declared in any specific module's declaration section, it is available to all modules, cursors, and other elements of the package.

In the following example of the sp_timer package (the full version is on the disk), the last_timing variable is a package-level global. It is referenced in both of the modules, capture and elapsed. The capture procedures sets the value of last_timing. The elapsed function uses the value of last_timing to compute the elapsed time.

/* Filename on companion disk: sptimer.sps. */ PACKAGE BODY sp_timer IS    /* Package variable which stores the last timing made */    last_timing NUMBER := NULL;     PROCEDURE capture (context_in IN VARCHAR2 := NULL)    /* Save current time and context to package variables. */    IS    BEGIN       last_timing := DBMS_UTILITY.GET_TIME;    END;     FUNCTION elapsed RETURN NUMBER IS    BEGIN       IF last_timing IS NULL       THEN          RETURN NULL;       ELSE          RETURN DBMS_UTILITY.GET_TIME - last_timing;       END IF;    END;  END sp_timer;

16.5.5 Providing an Interface to Global Data

You will often want to build a programmatic interface around your global data, due to the following drawbacks of global data:

• Loss of control. When you declare a data structure in the package specification, that variable (or PL/SQL table or whatever) becomes fair game for any program that wants to change it (unless it is declared as a CONSTANT). If a program can make changes without any controls, then you lose the ability to apply rules to the way the data is used. It is impossible to provide an object orientation to your application if this happens.

• Maintenance headaches. When you allow programmers to make direct references to package data structures, you are limiting your ability to change those data structures over time. Suppose that last year I implemented an in-memory profit-and-loss statement as a string variable filled with numbers padded to a length of ten. Suppose too that the total profit entry in the statement is the fourteenth number in the string. If I declared this string in my package specification, programmers who needed access to the statement would write a line of code like this:

  total_profit_loc := 14; total_profit := SUBSTR (pl.stmt, (total_profit_loc-1)*10 + 1, 10);

  Then along comes PL/SQL Release 2.3 and I can use PL/SQL tables of records to store my many P & L statements. Suddenly my hands are tied. If I change the underlying data structure of my P & L statement, every single program that directly accessed the filled string will have to be changed.

You can regain control of your package data and also ease your maintenance and enhancement frustrations by building a programmatic interface around your data. This interface is also referred to as "get and set programs" and "access routines," because they usually get and set the values of data and control access to those data structures.

In the preceding code, if I had instead declared the stmt string in the body of my pl package, I could have provided functions to retrieve the different elements of the statement as follows:

total_profit := pl.retrieve ('total_profit');

I would then be free to change the underlying data structure from filled string to PL/SQL table. As long as the interface to the pl.retrieve function did not change, none of the programs that relied on the old data would have to change. My programmatic interface would have protected both my data structure and all the programs that relied on it.

Consider the simple sp_timer package (the body of which was reviewed in the last section). The whole point of this package is to keep track of the elapsed time, down to the 100th of a second. It calculates the elapsed time by storing the starting point in a private global variable, last_timing. If a programmer could have direct access to this variable, he could change its value and disrupt the elapsed time calculation. So instead of defining last_timing in the specification, I place it in the body and instead provide two modules in the specification as follows:

PACKAGE sp_timer IS    PROCEDURE capture (context_in IN VARCHAR2 := NULL);     FUNCTION elapsed RETURN NUMBER;  END sp_timer;

The only way a developer can change last_timing is through the capture procedure. The only way a developer can retrieve the elapsed time is through the elapsed function. My data structure is protected.