Automatic recompilation

The third method, automatic recompilation, is even simpler, but it isn't without drawbacks. If you merely execute one of the programs implemented in the bookworm package body, Oracle will recompile it just prior to execution.

Production environments are likely to have strict policies on when you can and cannot recompile programs. That's because installing a new version of a PL/SQL program can force a lot of recompilation and can have disastrous side effects on programs currently running. You will probably want to run a script similar to utlrp.sql to find and recompile all invalid objects. Your DBA should help you schedule an upgrade procedure, which should include running the recompilation script.

Aside from a potentially unscheduled drain on performance, the biggest drawback to Oracle's automatic recompilation feature is that it can interfere with packages that are currently running. The telltale sign of this will be a series ofmessages:

ORA-04068: existing state of packages has been discardedORA-04061: existing state of package "SCOTT.P1" has been invalidatedORA-04065: not executed, altered or dropped package "SCOTT.P1"ORA-06508: PL/SQL: could not find program unit being called

This happens because automatically recompiling a package that is currently running invalidates the package. Not only that, Oracle shares the program's bytecode among different sessions, so this invalidation affects not just the session that triggered the recompilation, but every Oracle session that is running the package! Consequences of this devastation include the following:

· Executions terminate with an ORA-04068 error (unless programs have been written with some sophisticated exception handlers).

· All public and private package variables assume their default values; if you haven't supplied an explicit default value, it will revert to NULL. This effect applies to any package that the session has instantiated, not just the one that's getting recompiled. So, if you were in the middle of any computations that assign values to package variables, you will lose the contents of those variables.

· DBMS_OUTPUT stops working. Well, not really, but if you had previously enabled it, Oracle will disable it. This occurs because its on/off switch is really a package variable, and resetting the package reverts it to the default, which is off.

· Because all the packages executing in the current session have been reset, merely repeating the call should succeed; that is, the second call should not fail with the ORA-04068 error.

I have written a number of scripts attempting to demonstrate this behavior in various scenarios, and have wound up with some strangely inconsistent results—for example, a script that produces the error twice out of ten runs. I also have evidence, though, that recompiling the affected package with explicit ALTER...COMPILE statements reduces the ripple effect; instead of destroying the state of all packages for the current session, it seems to limit the "reset" to the package you're recompiling.

The bottom line on automatic recompilation bears repeating. In live production environments, do not do anything that will invalidate or recompile (automatically or otherwise) any stored objects for which sessions might have instantiations that will be referred to again. Also, use a recompilation script. Fortunately, development environments don't need to worry about ripple effects too much, and automatic recompilation outside of production is a huge plus.

20.2.3 Dependencies in Client-Side PL/SQL

If you're building applications using Oracle Forms and friends, a client-side PL/SQL program may have dependencies on any of the following:

· Other client-side PL/SQL programs

· Server-side objects such as tables, views, or PL/SQL programs

· Module-level items such as on-screen fields in Oracle Forms

· So-called "system variables" maintained by the development environment

You can attempt to explore these relationships by expanding the "References" items that appear under the program's name, as shown in Figure 20-6.

Figure 20-6. Viewing "References" information for a package body in a client-side PL/SQL library

Although the navigator successfully illustrates the external references for each client-side program unit, it does not show dependencies in client-side triggers. Moreover, the reverse dependency list—the "Referenced by" information—lists only referencing programs that happen to be inside the current module.

So, for example, let's say that you've created a client-side PL/SQL library that five different developers have been using in dozens of Oracle Forms modules. If you open up the library in the development environment and click on "Referenced by," you will see something like Figure 20-7.

Figure 20-7. "Referenced By" information shows only those dependencies in the current module

That is, you will see nothing. I don't know about you, but if I were thinking of changing the interface of a program in a PL/SQL library, I would want to know what it will break before I start getting calls from users. One workaround is to use an old-fashioned manual procedure, perhaps augmented by some brute-force file utilities like the Unix grep program.

In fairness to Oracle, let me say that the lack of referenced-by information isn't unique to client-side PL/SQL; anybody who releases a new version of a callable program has this issue. It does, however, help us appreciate the value of the information you can get from the data dictionary on the server!