Data Dependencies

When you create a stored procedure or function, Oracle verifies that the operations it performs are possible based on the schema objects accessed. For example, if a stored procedure contains a SELECT statement that selects columns from a table, Oracle verifies that the table exists and contains the specified columns. If the table is subsequently redefined so that one of its columns does not exist, the stored procedure may not work properly. For this reason, the stored procedure is said to depend on the table.
In cases such as this, Oracle automatically manages dependencies among schema objects. After a schema object is redefined, Oracle automatically recompiles all stored procedures and functions in your database that depend on the redefined object the next time they are called. This recompilation allows Oracle to verify that the procedures and functions can still execute properly based on the newly defined object.
Runtime recompilation reduces runtime performance and the possible resulting runtime compilation errors can halt your applications. Follow these measures to avoid runtime recompilation:
Do not redefine schema objects (such as tables, views, and stored procedures and functions) while your production applications are running. Redefining objects causes Oracle to recompile stored procedures and functions that depend on them.
After redefining a schema object, manually recompile dependent procedures, functions, and packages. This measure not only eliminates the performance impact of runtime recompilation, but it also notifies you immediately of compilation errors, allowing you to fix them before production use.
You can manually recompile a procedure, stored function, or package with the COMPILE option of the ALTER PROCEDURE, ALTER FUNCTION, or ALTER PACKAGE command.
Store procedures and functions in packages whenever possible. If a procedure or function is stored in a package, you can modify its definition without causing Oracle to recompile other procedures and functions that call it.
Packages are the most effective way of preventing unnecessary dependency checks from being performed.
The %TYPE attribute provides the datatype of a variable, constant, or column. This attribute is particularly useful when declaring a variable or procedure argument that refers to a column in a database table. The %ROWTYPE attribute is useful if you want to declare a variable to be a record that has the same structure as a row in a table or view, or a row that is returned by a fetch from a cursor.
Dependencies among PL/SQL library units (packages, stored procedures, and stored functions) can be handled either with timestamps or with signatures.
• In the timestamp method, the server sets a timestamp when each library unit is created or recompiled, and the compiled states of its dependent library units contain records of its timestamp. If the parent unit or a relevant schema object is altered, all of its dependent units are marked as invalid and must be recompiled before they can be executed.
• In the signature method, each compiled stored library unit is associated with a signature that identifies its name, the types and modes of its parameters, the number of parameters, and (for a function) the type of the return value. A dependent unit is marked as invalid if it calls a parent unit whose signature has been changed in an incompatible manner.
Oracle dynamically recompiles an invalid view or PL/SQL program unit the next time it is used. Alternatively, you can force the compilation of an invalid view or program unit using the appropriate SQL command with the COMPILE parameter.
Forced compilations are most often used to test for errors when it is known that a dependent view or program unit is invalid, but is not currently being used; therefore, automatic recompilation would not otherwise occur until the view or program unit is executed.