6 Dependencies Among Schema Objects

Introduction to Dependency Issues

Some types of schema objects can reference other objects as part of their definition. For example, a view is defined by a query that references tables or other views. A procedure's body can include SQL statements that reference other objects of a database. An object that references another object as part of its definition is called a dependent object, while the object being referenced is a referenced object. Figure 6-1 illustrates the different types of dependent and referenced objects:

Figure 6-1 Types of Possible Dependent and Referenced Schema Objects

Description of "Figure 6-1 Types of Possible Dependent and Referenced Schema Objects"

If you alter the definition of a referenced object, dependent objects may or may not continue to function without error, depending on the type of alteration. For example, if you drop a table, no view based on the dropped table is usable.

Oracle automatically records dependencies among objects to alleviate the complex job of dependency management for the database administrator and users. For example, if you alter a table on which several stored procedures depend, Oracle automatically recompiles the dependent procedures the next time the procedures are referenced (run or compiled against).

To manage dependencies among schema objects, all of the schema objects in a database have a status.

• Valid schema objects have been compiled and can be immediately used when referenced.

• Invalid schema objects must be compiled before they can be used.

  • For procedures, functions, and packages, this means compiling the schema object.

  • For views, this means that the view must be reparsed, using the current definition in the data dictionary.

  Only dependent objects can be invalid. Tables, sequences, and synonyms are always valid.

  If a view, procedure, function, or package is invalid, Oracle may have attempted to compile it, but errors relating to the object occurred. For example, when compiling a view, one of its base tables might not exist, or the correct privileges for the base table might not be present. When compiling a package, there might be a PL/SQL or SQL syntax error, or the correct privileges for a referenced object might not be present. Schema objects with such problems remain invalid.

Oracle automatically tracks specific changes in the database and records the appropriate status for related objects in the data dictionary.

Status recording is a recursive process. Any change in the status of a referenced object changes the status not only for directly dependent objects, but also for indirectly dependent objects.

For example, consider a stored procedure that directly references a view. In effect, the stored procedure indirectly references the base tables of that view. Therefore, if you alter a base table, the view is invalidated, which then invalidates the stored procedure. Figure 6-2 illustrates indirect dependencies:

Figure 6-2 Indirect Dependencies

Description of "Figure 6-2 Indirect Dependencies"

Resolution of Schema Object Dependencies

When a schema object is referenced directly in a SQL statement or indirectly through a reference to a dependent object, Oracle checks the status of the object explicitly specified in the SQL statement and any referenced objects, as necessary. Oracle's action depends on the status of the objects that are directly and indirectly referenced in a SQL statement:

• If every referenced object is valid, then Oracle runs the SQL statement immediately without any additional work.

• If any referenced view or PL/SQL program unit (procedure, function, or package) is invalid, then Oracle automatically attempts to compile the object.

  • If all invalid referenced objects can be compiled successfully, then they are compiled and Oracle runs the SQL statement.

  • If an invalid object cannot be compiled successfully, then it remains invalid. Oracle returns an error and rolls back the failing SQL statement. The rest of the transaction is unaltered and can be committed or rolled back by the user.

    Note:

    Oracle attempts to recompile an invalid object dynamically only if it has not been replaced since it was detected as invalid. This optimization eliminates unnecessary recompilations.

SELECT * FROM vtest;

ALTER VIEW vtest compile;

Object Name Resolution

Object names referenced in SQL statements can consist of several pieces, separated by periods. The following describes how Oracle resolves an object name.

1. Oracle attempts to qualify the first piece of the name referenced in the SQL statement. For example, in hr.employees, hr is the first piece. If there is only one piece, then the one piece is considered the first piece.

   1. In the current schema, Oracle searches for an object whose name matches the first piece of the object name. If it does not find such an object, then it continues with step b.

   2. Oracle searches for a public synonym that matches the first piece of the name. If it does not find one, then it continues with step c.

   3. Oracle searches for a schema whose name matches the first piece of the object name. If it finds one, then it returns to step b, now using the second piece of the name as the object to find in the qualified schema. If the second piece does not correspond to an object in the previously qualified schema or there is not a second piece, then Oracle returns an error.

   If no schema is found in step c, then the object cannot be qualified and Oracle returns an error.

2. A schema object has been qualified. Any remaining pieces of the name must match a valid part of the found object. For example, if hr.employees.department_id is the name, then hr is qualified as a schema, employees is qualified as a table, and department_id must correspond to a column (because employees is a table). If employees is qualified as a package, then department_id must correspond to a public constant, variable, procedure, or function of that package.

Because of how Oracle resolves references, it is possible for an object to depend on the nonexistence of other objects. This situation occurs when the dependent object uses a reference that would be interpreted differently were another object present.

Shared SQL Dependency Management

In addition to managing dependencies among schema objects, Oracle also manages dependencies of each shared SQL area in the shared pool. If a table, view, synonym, or sequence is created, altered, or dropped, or a procedure or package specification is recompiled, all dependent shared SQL areas are invalidated. At a subsequent execution of the cursor that corresponds to an invalidated shared SQL area, Oracle reparses the SQL statement to regenerate the shared SQL area.

Local and Remote Dependency Management

Tracking dependencies and completing necessary recompilations are performed automatically by Oracle. Local dependency management occurs when Oracle manages dependencies among the objects in a single database. For example, a statement in a procedure can reference a table in the same database.

Remote dependency management occurs when Oracle manages dependencies in distributed environments across a network. For example, an Oracle Forms trigger can depend on a schema object in the database. In a distributed database, a local view's defining query can reference a remote table.

BEGIN

UPDATE table set ...
invalid_proc;
COMMIT;

END;

UPDATE table set ...
EXECUTE invalid_proc;
COMMIT;