Oracle7 Server Application Developer's Guide
Dependency Issues
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.
Avoiding Runtime Recompilation
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. For more information on these commands, see the Oracle 7 Server SQL Reference manual.
Additional Information: The exact name and location of the UTLDTREE.SQL script may vary depending on your operating system. See this script for more information on how to use it.
- 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.
There are several dependency issues to consider before dropping a procedure or package. Additional information about dependency issues is included in the Oracle7 Server Concepts manual. Some guidelines for managing dependencies follow.
Use Packages Whenever Possible Packages are the most effective way of preventing unnecessary dependency checks from being performed. The following example illustrates this benefit.
Assume this situation:
- The standalone procedure PROC depends on a packaged procedure PACK_PROC.
- The PACK_PROC procedure's definition is altered by recompilation of the package body.
- The PACK_PROC procedure's specification is not altered in the package specification.
Even though the package's body is recompiled, the standalone procedure PROC that depends on the packaged procedure PACK_PROC is not invalidated, because the package's specification is not altered.
This technique is especially useful in distributed environments. If procedures are always part of a package, remote procedures that depend on packaged procedures are never invalidated unless a package specification is replaced or invalidated.
Whenever you recompile a procedure, you should consult with any other database administrators and application developers to identify any remote, dependent procedures and ensure that they are also recompiled. This eliminates recompilations at runtime and allows you to detect any compile-time errors that would otherwise be seen by the application user. See "Manually Recompiling Invalid Views, Procedures, Packages, and Triggers" ![[*]](jump.gif)
for more information.
The %TYPE and %ROWTYPE Attributes 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.
When you declare a construct using %TYPE and %ROWTYPE, you do not need to know the datatype of a column or structure of a table. For example, the argument list of a procedure that inserts a row into the EMP table could be declared as
CREATE PROCEDURE hire_fire(emp_record emp%ROWTYPE) AS ... END;
If you change the column or table structure, the constructs defined on their datatypes or structure automatically change accordingly.
However, while one type of dependency is eliminated using %TYPE or %ROWTYPE, another is created. If you define a construct using object%TYPE or object%ROWTYPE, the construct depends on object. If object is altered, the constructs that depend on object are invalidated.