2.10.1 Gaining Control of Your Data

I recommend, in fact, that you never define variables in the specification of a package (except when explicitly needed that way, as discussed at the end of this section). Instead, you always declare the variable in the package body. You then provide a procedure to set the value of that variable and a function to retrieve the value of that variable.

Let's look at a very simple example to drive home the point and then move on to more interesting applications of this practice. Suppose I have a profit-and-loss package that maintains a "last statement date" in a package variable. With the variable defined in the specification, my package looks like this:

PACKAGE P_and_L 
IS
   last_stmt_dt DATE;                                                        
END P_and_L;

Suppose further that I have a business rule that applies to the last statement date: it can never be in the future. Since the variable is defined in the package specification, any user with execute authority on this package can directly reference and modify the variable as shown in these code fragments:

P_and_L.last_stmt_dt := SYSDATE + 12;
v_newdate := P_and_L.last_stmt_dt;

In the first line, my code violates the business rule -- and there is nothing I can do to stop this violation.

Let's now move the last_stmt_dt inside the package body. When I do this, I must write some code to provide a programmatic interface to that date variable. The resulting package specification and body shown in Example 2.2 provide get-and-set routines to get the current value of the last statement date and also set the value of that variable.

Sure, this is a lot more code than was necessary to simply "publish" the last statement date variable in the package specification. The benefits of this code are, however, significant and will now be explored. First of all, notice that the set_last_date procedure applies or enforces the business rule whenever anyone tries to change the value of the last_stmt_dt variable. Let's examine the impact of this enforcement. With my packaged interface, the two lines of code I showed you earlier would be changed to:

P_and_L.set_last_date (SYSDATE + 12);
v_newdate := P_and_L.last_date;

Now instead of setting the last statement date to twelve days in the future, set_last_date intervenes and sets the date to the system date. (Of course, in the real world, you would probably not enforce a business rule by simply overriding a user action. For purposes of demonstration, however, it gets the point across.)

By moving last_stmt_dt to the inside of the package, I have exerted control over my package data. I can now guarantee the integrity of this data to any user of the package; you know what you are getting when you call the last_date function. In the first version of the P_and_L package, there was no way to know how the value was set.

This control and integrity is the most important benefit accrued from hiding your data in the body of the package. Many other wonderful advantages are possible, however, once you have taken this step. These are covered in the following sections.

2.10.2 Tracing Variable Reads and Writes

Have you ever lost control of your application? I once worked on an Oracle Forms application in which there was no doubt that the complexity of the code (and workarounds in the code) had caused it take on a life of its own. This application relied heavily on Oracle Forms GLOBAL variables -- to the tune of 400 or so of these useful, but dangerous constructs. And, sad to say, we could not, in a number of circumstances, figure out why and how a particular global was being set to NULL or to some other value that made no sense for the action at hand.

There had been no forethought in the use of the global variables. Everyone was scrambling to meet deadlines with a very early version of Oracle Forms (4.0.6 for those of you who know to shudder at such things) and just threw direct references to the globals willy-nilly throughout the code. There was no way, consequently, to trace where and when a global value was changed. If, on the other hand, the original developers of the application had built a package around the use of Oracle Forms globals, such a trace would have been very possible, and much agony would have been averted.

I demonstrate below the tracing technique for the P_and_L package. You can then apply this technique to Oracle Forms global variables and any other variable data structure.

Let's go back to the P_and_L package shown in Example 2.2 and the last statement date. The variable is declared in the package body. A function is provided to return the current value of last_stmt_dt . A procedure, set_last_date , allows me to change the variable's value. I build an application making many references to these programs and then I start testing that application. I soon run into trouble. The last statement date is being set improperly, but it is very difficult for me to figure out how and why its value is being changed.

What I would really like to do is obtain a trace of every contact with that variable. If I had not hidden the last statement date variable inside a package, my situation would be hopeless. I would have no way to know when my programs were touching the last statement date.

With my last_date function and set_last_date procedure in place, on the other hand, I can with just a few lines of code get all the information I need. In the upgraded version of the P_and_L package below, I use the PLVtrc package (see code in bold) to add an execution trace to the last statement date's get-and-set:

PACKAGE BODY P_and_L
IS
   last_stmt_dt DATE; 

   FUNCTION last_date RETURN DATE IS 
   BEGIN

      PLVtrc.show ('Retrieve last_date', last_stmt_dt);

      RETURN last_stmt_dt;
   END;

   PROCEDURE set_last_date (date_in IN DATE) IS
   BEGIN

      PLVtrc.show ('Set last_date', date_in);

      last_stmt_dt := LEAST (date_in/, SYSDATE);
   END;
END P_and_L;

The PLVtrc.show procedure intercepts attempts to read or write the last_stmt_dt variable. This trace is, however, not active, until the following command is used to turn on the trace for the current session:

PLVtrc.turn_on;

When she turns trace on, a developer can view (or write to the PL/Vision log) a record of every effort to read or write the variable. And if the PL/SQL programs that call the P_and_L package make use of the PLVtrc startup and terminate programs, this record will automatically include the names of the programs or context when the last_stmt_dt variable was referenced (see Chapter 20, PLVcmt and PLVrb: Commit and Rollback Processing ). Just a little bit of added code produced a significant enhancement in functionality!

Furthermore, all of my tracing changes occurred to the package body; the specification was left intact. As a result, none of the programs that call the P_and_L elements need to be changed or even recompiled. No one even has to know that the package has been upgraded with the new feature; it will be invisible until turned on -- and then only for the current Oracle session, not for all users.

Once I built the get-and-set around my date variable, adding an execution trace facility was very simple. Just get that layer of code in place and many seemingly and formerly impossible tasks become easy!

2.10.3 Simplifying Package Interfaces

Another reason for moving data into the package body is to simplify the interfaces to the package elements. When data are declared in the package body, they are global within the package . All programs defined in the package (specification and body) can reference these variables directly. You can use this fact to your advantage by not passing in these values in the parameter lists of the package elements.

Consider the PLVobj package, which provides a programmatic interface to the ALL_OBJECTS data dictionary view. PLVobj works with a current object, which is made up of three elements:

• The owner or the schema of the object

• The name of the object

• The type of the object

The PLVobj package and other packages such as PLVio, perform many different operations on this current object, including the following: bind the object for dynamic SQL execution, open a cursor into the ALL_OBJECTS view for this object, read the source code for that object, and so on.

Suppose that I did not store this current object in the package. Then every time I wanted to perform one of the above actions, I would have to provide the values for each of these elements of the current object in the parameter list. Let's look at some examples.

Instead of calling PLVobj.open_objects without any arguments like this:

PROCEDURE open_objects;

I would need to modify the header as follows:

PROCEDURE open_objects
   (name_in IN VARCHAR2, type_in IN VARCHAR2, schema_in IN VARCHAR2);

And deep within the PLVio package, I could no longer simply call the bindobj program relying on the context or current object previously set, as I do here:

PLVobj.bindobj (cur);

Instead, I would have to maintain variables inside PLVio with the current object values and then pass them into bindobj as follows:

PLVobj.bindobj (cur, currobj_name, currobj_type, currobj_schema);

Would you use a package designed that way? I don't think I would. All those arguments, passed in over and over again. Each time thinking: why can't the package just keep track of that for me?

Well, it can and PLVobj does just that. The current object of PLVobj is defined by three private package variables:

v_currschema

The owner of the object

v_currname

The name of the object

v_currtype

The type of the object(s)

Since the above elements are private variables, a user of PLVobj will never see or reference these variables directly. Instead, I provide a program to set the current object. Its header is:

PROCEDURE setcurr (name_in IN VARCHAR2);

where the argument is the module name, which can actually be a composite of the schema, name, and type.

With the setcurr procedure assigning values to my current object, the parameter lists of my object-management programs in PLVobj become short and sweet. They are much easier to use.

There is, of course, a tradeoff when you rely on package global data instead of passing parameters. Sure, the data is private and access to it is controlled. But it also means that the package program is completely dependent on that data. You cannot use the program to analyze or manipulate data until it is set into the package globals. The only way you can use the PLVobj package is to first call the setcurr procedure.

I believe that in many cases, this tradeoff is a good investment. It reinforces my perspective on the package as an environment more than simply a collection of related code elements.

2.10.4 When to Make Data Public

You shouldn't always hide your data in the package body. Sometimes you really do want to let someone directly access the information. I have found, for example, that if you are going to execute dynamically constructed PL/SQL code with the DBMS_SQL package and you want to reference any kind of external data directly, it must be defined in the specification of some package. Dynamically executed PL/SQL blocks are never nested within another block. As a result, they can only reference variables declared in the dynamic block or in a package specification (see Chapter 18, PLVcase and PLVcat: Converting and Analyzing PL/SQL Code , for more details).

Another place in PL/Vision where I violate this practice and declare data structures in the specification is the PLVio package. You can choose to use a PL/SQL table as a target with the following call:

PLVio.settrg (PLV.pstab);

Then all subsequent calls to PLVio.put_line will deposit information in another row of data in the PLVio-based PL/SQL table, defined in the specification as follows:

   target_table PLVtab.vc2000_table;
   target_row BINARY_INTEGER;

Why did I put this table in the specification? I suppose I could have hidden it away in the body and then built some programs that would maintain the contents of the table, along these lines:

   PROCEDURE init_table;
   PROCEDURE set_row (val_in IN VARCHAR2);
   FUNCTION rowval (row_in IN INTEGER) RETURN VARCHAR2;
   PROCEDURE display;

Maybe I just got lazy that night. But maybe, just maybe, it actually makes more sense in this case to allow the developer to do whatever she wants with the table and its contents. It is just a repository, after all, for the output from calls to the PLVio.put_line procedure. You might, in fact, want to write some information from PLVio and then add a few rows of data from your own, independent source. Rather than put up the barrier of get-and-set routines, I just leave the table in the specification and make the user responsible for its contents.

2.10.5 Anchoring to Public Variables

There is one other case in which specification-based variables are useful: anchored declarations. You can anchor or base the declaration of a variable on another, predefined structure. To do this, you use the %TYPE and %ROWTYPE attributes. The most common way %TYPE is used is to anchor a local PL/SQL variable to a database column, as shown below:

   v_ename emp.ename%TYPE;

You can also, however, anchor variables to other PL/SQL data structures. You can define variables in one package (a repository of subtypes) that are used to define variables in another package. In this case, the variables must be declared in the specification. An example from PL/Vision will demonstrate this technique.

A number of PL/Vision packages manipulate PL/SQL source code (PLVgen, PLVcase, PLVcat, etc.). One important element of PL/SQL code is the identifier . An identifier is a named element of the language. Today, identifiers can be up to 30 characters in length and must start with a letter.

As I built packages to read and parse identifiers (see PLVprsps), I would declare local variables to hold those values. At first, I declared the variable as follows:

v_ident VARCHAR2(30);

This always made me uncomfortable, though. I could just see Oracle Corporation in its next release announce that it would now allow identifiers to be up to, say, 60 characters in length. My code would instantly become very vulnerable. So I would often compensate by declaring the variable as:

v_ident VARCHAR2(100);

I felt safe, but dissatisfied. The justification for that declaration was weak; it would be hard (embarrassing?) to explain to another developer why I chose this number. After too many months, I found the ideal solution: use an anchored declaration.

So I added the following declaration to the PLV package specification:

plsql_identifier VARCHAR2(100) := 'IRRELEVANT';

I decided to use 100 because my identifier variable needed to hold identifiers of the form "package.element" and so that I had some extra space with which to work. I then changed my hard-coded declaration of v_ident and many other variables to this format:

v_ident PLV.plsql_identifier%TYPE;

Now if I ever do need to change the length or other characteristic of variables that represented PL/SQL identifiers, I could make that change in just one place. Notice that I assigned the default value of IRRELEVANT to the variable. I did that to emphasize that the value contained in plsql_identifier is irrelevant. It is never referenced (or intended to be referenced) for its value, only for its datatype.

NOTE: You might be thinking that I should just have declared plsql_identifier as a constant and then the value of this "reference only" structure could not be mucked with. That certainly makes sense. I found, however, that you cannot reference a constant in an anchored declaration. If I wanted to use plsql_identifier to anchor other variable declarations, it had to be declared a variable.

So there are certainly circumstances in which you will want to declare data structures in the package specification. This should occur, however, on an exception basis -- and you should be able to justify your action with some application-specific requirements. Otherwise, hide that package data in the body and you will reap many benefits.