|Oracle7 Server Application Developer's Guide||
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.
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:
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" 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.
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.
Invalid dependent objects can be identified by querying the data dictionary views USER_OBJECTS, ALL_OBJECTS, and DBA_OBJECTS; see "Listing Dependency Management Information" for examples.
ALTER VIEW emp_dept COMPILE;
ALTER PROCEDURE update_salary COMPILE;
SQLPLUS> ALTER PACKAGE acct_mgmt_package COMPILE BODY; SQLPLUS> ALTER PACKAGE acct_mgmt_package COMPILE PACKAGE;
All packages, procedures, and functions can be recompiled using the following syntax. The objects are compiled in dependency order, enabling each to be compiled only once.
SQLPLUS> EXECUTE DBMS_UTILITY.COMPILE_ALL;
ALTER TRIGGER reorder COMPILE;
Consider the following statements for Examples 1 and 2:
CREATE TABLE emp . . .; CREATE PROCEDURE hire_emp BEGIN . . . END; ALTER TABLE emp . . . ;
Example 1 Listing the Status of an Object
The ALL_OBJECTS data dictionary view lists information about all the objects available to the current user and the current status (that is, valid or invalid) of each object. For example, the following query lists the names, types, and current status of all objects available to the current user:
SELECT object_name, object_type, status FROM all_objects;
The following results might be returned:
OBJECT_NAME OBJECT_TYPE STATUS --------------- --------------- ------------ EMP TABLE VALID HIRE_EMP PROCEDURE INVALID
Example 2 Listing Dependencies
The DBA_DEPENDENCIES data dictionary view lists all dependent objects in the database and the objects on which they directly depend. For example, the following query lists all the dependent objects in JWARD's schema:
SELECT name, type, referenced_name, referenced_type FROM sys.dba_dependencies WHERE owner = 'JWARD';
If JWARD issued the example statements at the beginning of this section, the following results might be returned:
NAME TYPE REFERENCED_NAME REFERENCED_TYPE ----------- ------------ ------------------- --------------- HIRE_EMP PROCEDURE EMP TABLE
To create the dependency tracking utility, you must run the SQL script UTLDTREE.SQL. The location of this file is operating system dependent. The UTLDTREE.SQL script creates the following schema objects:
Structure: object_id NUMBER referenced_object_id NUMBER nest_level NUMBER seq# NUMBER
A temporary table used to store dependency information returned by the DEPTREE_FILL procedure.
Column names: nested_level, object_type, owner, object_name, seq#
A view that lists dependency information in the DEPTREE_TEMPTAB table. The parent object is listed with a NESTED_LEVEL of 0, and dependent objects are listed with a nested level greater than 0.
Column name: dependencies
A view that lists dependency information in the DEPTREE_TEMPTAB table. Output is in a graphical format, with dependent objects indented from the objects on which they depend.
A sequence used to uniquely identify sets of dependency information stored in the DEPTREE_TEMPTAB.
Syntax: DEPTREE_FILL(object_type CHAR, object_owner CHAR, object_name CHAR)
A procedure that first clears the DEPTREE_TEMPTAB table in the executor's schema, then fills the same table to indicate the objects that directly or indirectly depend on (that is, reference) the specified object. All objects that recursively reference the specified object are listed, assuming the user has permission to know of their existence.
These SQL statements show how the UTLDTREE utility can be used to track dependencies of an object. Assume the following SQL statements:
CONNECT scott/tiger; CREATE TABLE scott.emp ( .... ); CREATE SEQUENCE scott.emp_sequence; CREATE VIEW scott.sales_employees AS SELECT * FROM scott.emp WHERE deptno = 10; CREATE PROCEDURE scott.hire_salesperson (name VARCHAR2, job VARCHAR2, mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER) IS BEGIN INSERT INTO scott.sales_employees VALUES (scott.emp_sequence.NEXTVAL, name, job, mgr, hiredate, sal, comm, 10; END; CREATE PROCEDURE scott.fire_salesperson (emp_id NUMBER) IS BEGIN DELETE FROM scott.sales_employees WHERE empno = emp_id; END; SELECT * FROM scott.emp; SELECT * FROM scott.sales_employees; EXECUTE scott.hire_salesperson ('ARNALL', 'MANAGER', 7839, / SYSDATE, 1000, 500); EXECUTE scott.fire_salesperson (7934);
Assume that before SCOTT alters the EMP table, he would like to know all the dependent objects that will be invalidated as a result of altering the EMP table. The following procedure execution fills the DEPTREE_TEMPTAB table with dependency information regarding the EMP table (executed using Server Manager):
EXECUTE deptree_fill('TABLE', 'SCOTT', 'EMP');
The following two queries show the previously collected dependency information for the EMP table:
SELECT * FROM deptree; NESTED_LEV TYPE OWNER NAME SEQ# ---------- ---------- -------------- ------------------ ---- 0 TABLE SCOTT EMP 0 1 VIEW SCOTT SALES_EMPLOYEES 1 2 PROCEDURE SCOTT FIRE_SALESPERSON 2 2 PROCEDURE SCOTT HIRE_SALESPERSON 3 SELECT * FROM ideptree; DEPENDENCIES ---------------------------------------- TABLE SCOTT.EMP VIEW SCOTT.SALES_EMPLOYEES PROCEDURE SCOTT.FIRE_SALESPERSON PROCEDURE SCOTT.HIRE_SALESPERSON
Alternatively, you can reveal all of the cursors that depend on the EMP table (dependent shared SQL areas currently in the shared pool) using the UTLDTREE utility. After connecting as INTERNAL and collecting dependency information for the table SCOTT.EMP, issue the following two queries:
SELECT * FROM deptree; NESTED_LEV TYPE OWNER NAME SEQ# ---------- ------ -------- ---------------------------- ---- 0 TABLE SCOTT EMP 0 1 CURSOR <shared> "select * from scott.emp 0.5 2 CURSOR <shared> "select * from scott.sa. . . 7.5 3 CURSOR <shared> "BEGIN hire_salesperson. . . 9.5 3 CURSOR <shared> "BEGIN fire_salesperson. . . 8.5 SELECT * FROM ideptree; DEPENDENCIES ------------------------------------------------------------ TABLE STEVE.EMP CURSOR <shared>."select * from scott.emp" CURSOR <shared>."select * from scott.sales_employee" CURSOR <shared>."BEGIN hire_salesperson ('ARN. . . CURSOR <shared>."BEGIN fire_salesperson (7934) END"
Copyright © 1996 Oracle Corporation.
All Rights Reserved.