An Introduction to Dependency Issues

Figure 16 - 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 can be used.

Oracle automatically records dependencies among objects to alleviate the complex job of dependency management from 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 (executed or compiled against).

To manage dependencies among objects, all schema objects in a database have a status:

INVALID	The object must be compiled before it can be used. In the case of procedures, functions, and packages, this means compiling the object. In the case of 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 there were some errors relating to the object. 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. Objects with such problems remain invalid.
VALID	The object has been compiled and can be immediately used when referenced.

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 not only changes the status 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 table(s) of that view. Therefore, if you alter a base table, the view is invalidated, which then invalidates the stored procedure. Figure 16 - 2 illustrates this.

Figure 16 - 2. Indirect Dependencies

When an object is referenced (directly in a SQL statement or indirectly via 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. Depending on the status of the objects that are directly and indirectly referenced in a SQL statement, different events can occur.

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

• If any referenced view or procedure (including functions and packages) is invalid, Oracle automatically attempts to compile the object. If all referenced objects that are invalid can be successfully compiled, the objects are compiled, and the SQL statement executes successfully. If an invalid object cannot be successfully compiled, the object remains invalid, an error is returned, and the transaction containing the SQL statement is rolled back.

Compiling Views and PL/SQL Program Units

• The definition of the view or program unit is correct; all SQL and PL/SQL statements must be proper constructs.

• All referenced objects are present and of the expected structure. For example, if the defining query of a view includes a column, the column must be present in the base table.

• The owner of the view or program unit has the necessary privileges for the referenced objects. For example, if a SQL statement in a procedure inserts a row into a table, the owner of the procedure must have the INSERT privilege for the referenced table.

Views and Base Tables

Attempting to use an invalid view automatically causes Oracle to recompile the view dynamically. After replacing the view, the view might be valid or invalid, depending on the following:

• All base tables referenced by the defining query of a view must exist. Therefore, if a base table of a view is renamed or dropped, the view is invalidated and cannot be used. References to invalid views cause the referencing statement to fail. The view can be compiled only if the base table is renamed to its original name or the base table is re-created.

• If a base table is altered or re-created with the same columns, but the datatype of one or more columns in the base table is changed, any dependent view can be recompiled successfully.

• If a base table of a view is altered or re-created with at least the same set of columns, the view can be validated. The view cannot be validated if the base table is re-created with new columns and the view references columns no longer contained in the re-created table. The latter point is especially relevant in the case of views defined with a "SELECT * FROM . . ." query, because the defining query is expanded at view creation time and permanently stored in the data dictionary.

Program Units and Referenced Objects

• If the referenced table is altered, the dependent procedure is invalidated.

• If the base table of the referenced view is altered, the view and the dependent procedure are invalidated.

• If the referenced standalone procedure is replaced, the dependent procedure is invalidated.

• If the body of the referenced package is replaced, the dependent procedure is not affected. However, if the specification of the referenced package is replaced, the dependent procedure is invalidated.

Security Authorizations