Managing Dependencies

When a procedure queries a table, calls another procedure, or uses a type, it depends on that object. Oracle tracks these relationships automatically and marks dependent code INVALID when something it relies on changes. Understanding dependency management is what keeps a large PL/SQL codebase compiling cleanly through schema changes.

Dependent and Referenced Objects

The referenced object is the one being used (a table, view, type, or another subprogram).
The dependent object is the one that uses it (your procedure, function, package, trigger, or view).

Oracle records every relationship in the data dictionary. When a referenced object changes in a way that could affect its dependents, Oracle invalidates those dependents.

CREATE OR REPLACE PROCEDURE list_emps IS
BEGIN
    FOR r IN (SELECT first_name, salary FROM employees) LOOP
        DBMS_OUTPUT.PUT_LINE(r.first_name || ': ' || r.salary);
    END LOOP;
END list_emps;
/
-- list_emps now DEPENDS ON the EMPLOYEES table.

-- A structural change to EMPLOYEES invalidates list_emps:
ALTER TABLE employees MODIFY (first_name VARCHAR2(50));

Viewing Dependencies

The *_DEPENDENCIES dictionary views expose the relationships:

-- What does LIST_EMPS depend on?
SELECT referenced_owner, referenced_name, referenced_type
FROM   user_dependencies
WHERE  name = 'LIST_EMPS'
AND    type = 'PROCEDURE';

-- What depends ON the EMPLOYEES table? (who breaks if I change it)
SELECT owner, name, type
FROM   all_dependencies
WHERE  referenced_name = 'EMPLOYEES'
AND    referenced_type = 'TABLE';

For a full recursive tree (direct and indirect dependents), Oracle ships the utldtree.sql script, which builds the DEPTREE and IDEPTREE views around DBMS_UTILITY.GET_DEPENDENCY.

Finding and Fixing Invalid Objects

-- List everything currently invalid
SELECT object_name, object_type, status
FROM   user_objects
WHERE  status = 'INVALID'
ORDER BY object_type, object_name;

An invalid object is automatically recompiled the next time it is used — if that recompile succeeds, callers never notice. If it fails (because the schema change genuinely broke the code), the caller gets the compile error. You can recompile proactively rather than waiting for first use:

-- Recompile one object
ALTER PROCEDURE list_emps COMPILE;
ALTER PACKAGE  emp_mgr    COMPILE;         -- spec + body
ALTER PACKAGE  emp_mgr    COMPILE BODY;    -- body only

-- Recompile every invalid object in the schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);

-- Database-wide (DBA): the utlrp.sql script recompiles all invalid objects
-- @?/rdbms/admin/utlrp.sql

Package Spec vs Body: The Key to Stable Dependencies

Dependents depend on a package's specification, not its body. This is the single most important dependency rule in PL/SQL: you can rewrite and recompile a package body as often as you like, and nothing that calls the package is invalidated — as long as the spec is unchanged.

-- Callers depend on emp_mgr's SPEC.
-- Recompiling the BODY does NOT invalidate them:
ALTER PACKAGE emp_mgr COMPILE BODY;   -- callers stay VALID

-- Changing the SPEC (adding/altering a public signature) DOES
-- invalidate every dependent:
CREATE OR REPLACE PACKAGE emp_mgr AS ... END;   -- cascades invalidation

✓

This is why packaging matters for large systems: group implementation behind a stable spec, and day-to-day body changes never ripple out as invalidations across the schema. Standalone procedures don't have this insulation — every change re-exposes the whole signature.

Fine-Grained Dependency Tracking (11g+)

Since Oracle 11g, dependency tracking is fine-grained: Oracle records dependencies down to the individual element, so changes that cannot possibly affect a dependent no longer invalidate it. For example:

Adding a column to a table no longer invalidates a procedure that SELECTs specific other columns.
Adding a new subprogram to a package spec no longer invalidates existing callers of the other subprograms.

Before 11g, either change would have invalidated every dependent. Fine-grained tracking dramatically reduces needless recompilation cascades on busy systems.

ℹ

Fine-grained tracking still has limits: SELECT * depends on the entire column list, so adding a column does invalidate a unit that uses SELECT * against that table. Selecting explicit columns keeps dependencies tight.

Remote Dependencies

When a local unit calls a subprogram over a database link, Oracle checks whether the remote object has changed using REMOTE_DEPENDENCIES_MODE:

Mode	Behaviour
`TIMESTAMP`	Recompile if the remote object's timestamp differs — can cause spurious invalidations across environments
`SIGNATURE` (default)	Recompile only if the remote signature (parameter list/return type) changed — more stable across builds

Summary

A dependent object uses a referenced object; Oracle tracks the relationship and invalidates dependents when referenced objects change.
Inspect relationships with USER_/ALL_/DBA_DEPENDENCIES; build a recursive tree with utldtree.sql (DEPTREE/IDEPTREE).
Invalid objects auto-recompile on next use; recompile proactively with ALTER … COMPILE, DBMS_UTILITY.COMPILE_SCHEMA, or utlrp.sql.
Dependents depend on a package spec, not its body — recompiling the body doesn't cascade invalidations.
Fine-grained dependency tracking (11g+) avoids invalidating units unaffected by a change; SELECT * opts out of that benefit.
REMOTE_DEPENDENCIES_MODE (SIGNATURE by default) governs how cross-database-link dependencies are validated.