Figure 20-5. Dependency graph of the bookworm package

In other words, Figure 20-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.

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 upon its body. Nothing depends upon 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 PROCEDURETEST_BOOK PACKAGE BODYBOOK PACKAGE BODYBOOKWORM PACKAGE BODYFORMSTEST 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, you'll be even happier to hear that a query on the referenced_name column is much faster than the earlier query on the same 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 local stored 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 object—even if you just rebuild it with no changes—will cause Oracle to invalidate any programs that reference that object (see the later sidebar A Little Validation, Please?). 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 INVALIDBOOK PACKAGE BODY INVALIDBOOKWORM PACKAGE BODY INVALIDFORMSTEST PACKAGE INVALIDFORMSTEST PACKAGE BODY INVALIDTEST_BOOK PACKAGE BODY INVALID

By the way, this again illustrates a benefit of the two-part package arrangement: as the query shows, the package bodies appear in this list of invalids, but the specs do not. This is a wonderful thing; if the specs had become invalid too, everything dependent on those would also be marked invalid, and so on.

Using packages in your design can break the cycle of dependencies and recompilations.

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. The Oracle client-side PL/SQL environment also includes a version of STANDARD, which contains the same essential ingredients as the server version. (There is also a package of extensions to STANDARD that include tool-specific programs such as CALL_FORM.)

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 PROCEDURE SCOTT.ADD_BOOK PACKAGE BODY SCOTT.BOOK PACKAGE BODY SCOTT.TEST_BOOK PACKAGE BODY SCOTT.BOOKWORM PACKAGE SCOTT.FORMSTEST PACKAGE BODY SCOTT.FORMSTEST

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.