Section 23.3.  Dependency Management

23.3. Dependency Management Another important phase of PL/SQL compilation and execution is the checking of program dependencies , defined as follows. A dependency (in PL/SQL) is a reference from a stored program to some Oracle object outside the program. Server-based PL/SQL programs can have dependencies on tables, views, types, procedures, functions, sequences, synonyms, or package specifications, but not on package bodies or type bodies. Client-based PL/SQL programs can have additional dependencies on items such as form fields that exist only in the client-side module. Oracle's basic dependency objective for PL/SQL is, loosely speaking: Do not allow a program to run if any of the objects on which it depends have changed since it was compiled. The good news is that most dependency management happens automatically, from the tracking of dependencies to the recompilation required to keep everything synchronized. You can't completely ignore this topic, though, and the following sections should help you understand how, when, and why you'll need to intervene. 23.3.1. Dependencies in Server-Side PL/SQL You can use the server's data dictionary to explore dependency relationships in quite a bit of detail. Here's a simple illustration of this rule in action, using the data dictionary to give us eyes into the database. Let's say that I have a package named bookworm on the server. In this package is a function that selects from the books table. If I create the table and then create the package, I expect to see the following: SQL> SELECT object_name, object_type, status 2 FROM USER_OBJECTS 3 WHERE object_name = 'BOOKWORM'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------ ------- BOOKWORM PACKAGE VALID BOOKWORM PACKAGE BODY VALID That is, there are two objects with the name BOOKWORM; the first is the package spec, and the second is the body. Right now, they're both VALID. Behind the scenes, Oracle has used its DIANA to determine a list of other objects that BOOKWORM needs in order to compile successfully. I can explore this dependency graph using a somewhat expensive query of the data dictionary view USER_DEPENDENCIES: SQL> SELECT name, type, referenced_name, referenced_type 2 FROM USER_DEPENDENCIES 3 WHERE name = 'BOOKWORM'; NAME TYPE REFERENCED_NAME REFERENCED_TYPE --------------- -------------- --------------- --------------- BOOKWORM PACKAGE STANDARD PACKAGE BOOKWORM PACKAGE BODY STANDARD PACKAGE BOOKWORM PACKAGE BODY BOOKS TABLE BOOKWORM PACKAGE BODY BOOKWORM PACKAGE Figure 23-5 illustrates this information as a directed graph, where the arrows indicate a "depends-on" relationship. In other words, Figure 23-5 shows that: The bookworm package specification and body both depend on the built-in package named STANDARD (see the sidebar "Flying the STANDARD"). The bookworm package body depends on its corresponding specification and on the books table. Figure 23-5. Dependency graph of the bookworm package For purposes of tracking dependencies, Oracle records the package specification and body as two different entities. Every package body will have a dependency on its corresponding specification, but the spec will never depend on its body. Nothing depends on the body. Hey, it might not even have a body. If you've done much software maintenance in your life, you will know that performing impact analysis relies not so much on "depends-on" information as it does on "referenced-by" information. Let's say that I'm contemplating a change in the structure of the books table. Naturally, I'd like to know everything that might be affected: SQL> SELECT name, type 2 FROM USER_DEPENDENCIES 3 WHERE referenced_name = 'BOOKS' 4 AND referenced_type = 'TABLE'; NAME TYPE ------------------------------ ------------ ADD_BOOK PROCEDURE TEST_BOOK PACKAGE BODY BOOK PACKAGE BODY BOOKWORM PACKAGE BODY FORMSTEST PACKAGE Apparently, in addition to the bookworm package, there are some programs in my schema I haven't told you about, but fortunately Oracle never forgets. Nice! (By the way, a query on the referenced_name column is much faster than the earlier query on the "name" column.) As clever as Oracle is at keeping track of dependencies, it isn't clairvoyant: in the data dictionary, Oracle can only track dependencies of local stored objects written with static calls. There are plenty of ways that you can create programs that do not appear in the USER_DEPENDENCIES view. These include external programs that embed SQL or PL/SQL; remote stored procedures or client-side tools that call local stored objects; and any programs that use dynamic SQL. As I was saying, if I alter the table's structure by adding a column: ALTER TABLE books ADD popularity_index NUMBER; then Oracle will immediately and automatically mark everything that references the books table, including the bookworm package body, as INVALID. Any change in the DDL time of an objecteven if you just rebuild it with no changeswill cause Oracle to invalidate any programs that reference that object (see the later sidebar "Avoiding Those Invalidations."). Actually, Oracle's automatic invalidation is even more sophisticated than that; if you own a program that performs a particular DML statement on a table in another schema, and your privilege to perform that operation gets revoked, this action will also invalidate your program. After the change, this is what I have: SQL> SELECT object_name, object_type, status 2 FROM USER_OBJECTS 3 WHERE status = 'INVALID'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------ ------- ADD_BOOK PROCEDURE INVALID BOOK PACKAGE BODY INVALID BOOKWORM PACKAGE BODY INVALID FORMSTEST PACKAGE INVALID FORMSTEST PACKAGE BODY INVALID TEST_BOOK PACKAGE BODY INVALID By the way, this again illustrates a benefit of the two-part package arrangement: for the most part, the package bodies have been invalidated, but not the specs, which can reduce the amount of recompilation required. The only spec that has been invalidated here is for FORMSTEST, which depends on the books table because (as I happen to know) it uses the anchored declaration books%ROWTYPE. Using packages in your design can break the cycle of dependencies and recompilations. One final note: another way to look at programmatic dependencies is to use Oracle's DEPTREE_FILL procedure in combination with the DEPTREE or IDEPTREE views. As a quick example, if I run the procedure using: SQL> EXEC DEPTREE_FILL('TABLE', 'SCOTT', 'BOOKS') I can then get a nice listing by selecting from the IDEPTREE view: SQL> SELECT * FROM IDEPTREE; DEPENDENCIES ------------------------------------------- TABLE SCOTT.BOOKS PROCEDUE SCOTT.ADD_BOOK PACKAGE BODY SCOTT.BOOK PACKAGE BODY SCOTT.TEST_BOOK PACKAGE BODY SCOTT.BOOKWORM PACKAGE SCOTT.FORMSTEST PACKAGE BODY SCOTT.FORMSTEST Flying the STANDARD All but the most pathologically ill Oracle installations will have a built-in package named STANDARD available in the database. This package contains many of the core features of the PL/SQL language, including: Functions such as INSTR and LOWER Comparison operators such as NOT, =, and > Predefined exceptions such as DUP_VAL_ON_INDEX and VALUE_ERROR Subtypes such as STRING and INTEGER You can view the source code for this package by looking at the file standard.sql, which you would normally find in the $ORACLE_HOME/rdbms/admin subdirectory. STANDARD's specification is the "root" of the PL/SQL dependency graph; that is, it depends upon no other PL/SQL programs, but most PL/SQL programs depend upon it. If you were to recompile the STANDARD specification, Oracle would invalidate virtually all the PL/SQL in the database. This listing shows the result of a recursive "referenced-by" query. If you want to run this built-in yourself, execute the utldtree.sql script (from the rdbms/admin subdirectory) to build the utility procedure and views in your own schema. Or, if you prefer, you can emulate it with a query such as: SELECT RPAD (' ', 3*(LEVEL-1)) || name || ' (' || type || ') ' FROM user_dependencies CONNECT BY PRIOR RTRIM(name || type) = RTRIM(referenced_name || referenced_type) START WITH referenced_name = 'name' AND referenced_type = 'type'; Now that we have seen how the server keeps track of relationships among objects, let's explore one way that Oracle takes advantage of such information. 23.3.2. Healing Invalids In addition to becoming invalid when a referenced object changes, a new program may be in an invalid state as the result of a failed compilation. In any event, no PL/SQL program marked as invalid will run until a successful recompilation restores its validity. There are at least three ways that can happen: By hand Using an explicit ALTER command to recompile the package By script Using a program that looks for invalid packages and issues the ALTER commands for you Automatically Relying on Oracle's built-in recompilation rules 23.3.2.1 Recompiling by hand The "by hand" method is simple, but it can be tedious. In the case presented earlier, I know by looking in the data dictionary that I need to recompile three stored programs, so I can just do this: ALTER PACKAGE bookworm COMPILE BODY; ALTER PACKAGE book COMPILE BODY; ALTER PROCEDURE add_book COMPILE; However, merely recompiling may be insufficient to recover from changes in the signature of a called program. You may have to edit the caller first! 23.3.2.2 Recompiling by script Various scripts exist that can help you reduce the tedium of these recompilations. Later versions of Oracle provides several related scripts in the usual rdbms/admin subdirectory. Note that these must be run while logged in to the database as SYSDBA. utlip.sql Invalidates and recompiles all PL/SQL code and views in the entire database. Actually, it sets up some data structures, invalidates the objects, and prompts you to restart the database and run utlrp.sql. utlrp.sql Recompiles all of the invalid objects in serial and is appropriate for single-processor hardware. If you have a multiprocessor machine, you probably want to use utlrcmp.sql instead. utlrcmp.sql Like utlrp.sql, recompiles all invalid objects, but in parallel; it works by submitting multiple recompilation requests into Oracle's job queue. You can supply the "degree of parallelism" as an integer argument on the command line. If you leave it null or supply "0", then the script will attempt to select the proper degree of parallelism on its own. However, even Oracle warns that this parallel version may not yield dramatic performance results because of write contention on system tables. There is another recompile utility, written by Solomon Yakobson, which can be used by non-DBAs after the DBA installs it. It handles stored programs, views (including materialized views), triggers, user-defined object types, and dimensions. Although written for Oracle7 and Oracle8 Database, it should work for most objects in later versions of Oracle. You can find the utility in a file named recompile.sql on the book's web site. Avoiding Those Invalidations When a database object's DDL time changes, Oracle's usual modus operandi is to immediately invalidate all of its dependents on the local database. Prior to 8.1.7.3, recreating a stored object without any actual changes in the code would always invalidate its dependent objects. In 8.1.7.3 Oracle introduced a way to do conditional invalidation of PL/SQLthat is, to invalidate dependents only if the object's source code has actually changed. As of Oracle Database 10g, my testing indicates that this feature is turned on by default. Recompiling a stored program via its original creation script will not invalidate dependents. This feature does not extend to recompiling a program using ALTER ... COMPILE or via automatic recompilation, which will invalidate dependents. Note that even if you use a script, Oracle Database 10g is very picky; if you change anything in your source codeeven just a single letterthat program's dependents will be marked invalid. So, if you're running at least 8.1.7.3 but have not yet made it to Oracle Database 10g, and you want to try it out, be warned that this is one of Oracle's undocumented and unsupported featuresnot for use by customers unless so advised by Oracle Support. That means you're completely out of luck if it breaks something. The way to turn on the feature for the current session is: SQL> ALTER SESSION SET EVENTS 2 '10520 TRACE NAME CONTEXT FOREVER, LEVEL 10'; Session altered. Now install your new code, and run a recompilation script such as $ORACLE_HOME/rdbms/admin/utlrp.sql. Then, turn off the feature: SQL> ALTER SESSION SET EVENTS 2 '10520 TRACE NAME CONTEXT OFF'; Session altered. Just don't do it on a production database...unless Oracle Support says it's safe. Note that this feature works only when rebuilding the called object from a source file. It does not stop invalidation of the caller when the called object gets recompiled with ALTER...COMPILE. 23.3.2.3 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 a 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 of messages: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package "SCOTT.P1" has been invalidated ORA-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 has instantiated 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 state causes this variable to revert to its 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. Unless you expose and/or record the error numbers, it could look like some weird intermittent problem. I have written a number of scripts attempting to demonstrate this behavior in various scenarios, and have wound up with some strangely inconsistent resultsfor example, a script that produces the error twice out of 10 runs. I also have evidence, though, that recompiling the affected package with explicit ALTER...COMPILE commands 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. 23.3.3. Remote Dependencies Server-based PL/SQL immediately becomes invalid whenever there's a change in a local object on which it depends. However, if it depends on an object on a different computer and that object changes, Oracle does not attempt to invalidate the calling PL/SQL program in real time. Instead, Oracle defers the checking until runtime. Here is a program that has a remote dependency on the procedure recompute_prices which lives across the database link findat.ldn.world: PROCEDURE synch_em_up (tax_site_in IN VARCHAR2, since_in IN DATE) IS BEGIN IF tax_site_in = 'LONDON' THEN recompute_prices@findat.ldn.world(cutoff_time => since_in); END IF; If you recompile the remote procedure and some time later try to run synch_em_up, you are likely to get an ORA-04062 error with accompanying text such as timestamp (or signature) of package "SCOTT.recompute_prices" has been changed. If your call is still legal, Oracle will recompile synch_em_up, and if it succeeds, its next invocation should run without error. To understand Oracle's remote procedure call behavior, you need to know that the PL/SQL compiler always stores two kinds of information about each referenced remote procedure: its timestamp and its signature: Timestamp The most recent date and time (down to the second) when an object's specification was reconstructed, as given by the TIMESTAMP column in the USER_OBJECTS view. For PL/SQL programs, this is not necessarily the same as the most recent compilation time because it's possible to recompile an object without reconstructing its specification. (Note that this column is of the DATE datatype, not the newer datatype that also happens to have the name TIMESTAMP.) Signature A footprint of the actual shape of the object's specification. Signature information includes the object's name and the ordering, datatype family, and mode of each parameter. So when I compiled synch_em_up, Oracle retrieved both the timestamp and the signature of the remote procedure called recomputed_prices, and stored a representation of them with the bytecode of synch_em_up. How do you suppose Oracle uses this information at runtime? The model is simple: it uses either the timestamp or the signature, depending on the current value of the parameter REMOTE_DEPENDENCIES_MODE. If that timestamp or signature information, which is stored in the local program's bytecode, doesn't match the actual value of the remote procedure at runtime, you get the ORA-04062 error. Oracle's default remote dependency mode is the timestamp method, but this setting can sometimes cause unnecessary recompilations. The DBA can change the system-wide setting via the database initialization file or an ALTER SYSTEM command; an application developer can set it for the current session using the following command: ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE; or, inside PL/SQL: EXECUTE IMMEDIATE 'ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE'; Thereafter, for the remainder of that session, every PL/SQL program run will use the signature method. As a matter of fact, Oracle's client-side tools always execute this ALTER SESSION ... SIGNATURE command as the first thing they do after connecting to the database, overriding whatever is in the Oracle initialization file. Oracle recommends using signature mode on client tools like Oracle Forms and timestamp mode on server-to-server procedure calls. Be aware that signature mode can cause false negativessituations where the runtime engine thinks that the signature hasn't changed, but it really hasin which case Oracle does not force an invalidation of a program that calls it remotely. You can wind up with silent computational errors that are difficult to detect and even more difficult to debug. Here are several risky scenarios: Changing only the default value of one of the called program's formal parameters. The caller will continue to use the old default value. Adding an overloaded program to an existing package. The caller will not bind to the new version of the overloaded program even if it is supposed to. Changing just the name of a formal parameter. The caller may have problems if it uses named parameter notation. In these cases, you will have to perform a manual recompilation of the caller. In contrast, the timestamp mode, while prone to false positives, is immune to false negatives. In other words, it won't miss any needed recompilations, but it may force recompilation that is not strictly required. This safety is no doubt why Oracle uses it as the default for server-to-server RPCs. If you do use the signature method, Oracle recommends that you add any new functions or procedures at the end of package specifications because doing so reduces false positives. In the real world, minimizing recompilations can make a significant difference in application availability. It turns out that you can trick Oracle into thinking that a local call is really remote so that you can use signature mode. This is done using a loopback Limitations of Oracle's Remote Invocation Model With the Oracle software available at the time of this writing, there is no direct way for any PL/SQL program to directly use any of the following package constructs on a remote server: Variables (including constants) Cursors Exceptions This limitation applies not only to client PL/SQL calling the database server, but also to server-to-server RPCs. The simple workaround for variables is to use "get-and-set" programs to encapsulate the data. In general, you should be doing that anyway because it's an excellent programming practice. The workaround for cursors is to encapsulate them with open, fetch, and close subprograms. For example, if you've declared a book_cur cursor in the specification of the book_maint package, you could put this corresponding package body on the server: CREATE OR REPLACE PACKAGE BODY book_maint AS prv_book_cur_status BOOLEAN; PROCEDURE open_book_cur IS BEGIN IF NOT book_maint.book_cur%ISOPEN THEN OPEN book_maint.book_cur; END IF; END; FUNCTION next_book_rec RETURN books%ROWTYPE IS l_book_rec books%ROWTYPE; BEGIN FETCH book_maint.book_cur INTO l_book_rec; prv_book_cur_status := book_maint.book_cur%FOUND; RETURN l_book_rec; END; FUNCTION book_cur_is_found RETURN BOOLEAN IS BEGIN RETURN prv_book_cur_status; END; PROCEDURE close_book_cur IS BEGIN IF book_maint.book_cur%ISOPEN THEN CLOSE book_maint.book_cur; END IF; END; END; Unfortunately, this approach won't work around the problem of using remote exceptions; the exception "datatype" is treated differently from true datatypes. Instead, you can use the RAISE_APPLICATION_ERROR procedure with a user-defined exception number between -20000 and -20999. See Chapter 6 for a discussion of how to write a package to help your application manage this type of exception. database link inside a synonym. Assuming that you have an Oracle Net service name "localhost" that connects to the local database, here is a schematic example: CREATE DATABASE LINK loopback CONNECT TO bob IDENTIFIED BY swordfish USING 'localhost'; CREATE OR REPLACE PROCEDURE volatilecode AS BEGIN -- whatever END; / CREATE OR REPLACE SYNONYM volatile_syn FOR volatilecode@loopback; CREATE OR REPLACE PROCEDURE save_from_recompile AS BEGIN ... volatile_syn; ... END; / To take advantage of this arrangement, your production system would then include an invocation such as this: BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET REMOTE_DEPENDENCIES_MODE SIGNATURE'; save_from_recompile; END; / As long as you don't do anything that alters the signature of volatilecode, you can modify and recompile it without invalidating save_from_recompile or causing a runtime error. You can even rebuild the synonym against a different procedure entirely. This approach isn't completely without drawbacks; for example, if volatilecode outputs anything using DBMS_OUTPUT, you won't see it unless save_from_recompile retrieves it explicitly over the database link and then outputs it directly. But for many applications, such workarounds are a small price to pay for the resulting increase in availability.