Dependency Management - Dependencies in Server-Side PL/SQL

If you’re working with server-side PL/SQL programs, you can use the server’s data dictionary to explore usage 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 you have a package named bookworm on the server. In this package is a function that selects from the books table. If you create the table and then create the package, 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. You can explore this dependency graph using a somewhat expensive (that is, slow) 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

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. 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 “depends-on” information as it does on “referenced-by” information. Let’s say that you're contemplating a change in the structure of the books table. Naturally, you'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 the schema that you didn't know about, but fortunately Oracle never forgets.
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.
Now if you 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. 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                     ROCEDURE         INVALID
BOOK                         PACKAGE BODY     INVALID
BOOKWORM                     PACKAGE BODY     INVALID
FORMSTEST                     PACKAGE             INVALID
FORMSTEST                     PACKAGE BODY     INVALID
TEST_BOOK                     PACKAGE BODY     INVALID

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.
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 you were to run the procedure using:

SQL> EXEC DEPTREE_FILL('TABLE', 'SCOTT', 'BOOKS')

You would 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';