20.2.1 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 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 (that is, slow) query of the data dictionary viewUSER_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 20-5 illustrates this information as a directed graph, where the arrows indicate a "depends-on" relationship.

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 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, 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 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: 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.