Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 15
Dependencies Among Schema Objects

The definitions of certain schema objects, such as views and procedures, reference other schema objects, such as tables. Therefore, some schema objects are dependent upon the objects referenced in their definitions. This chapter discusses how to manage the dependencies among schema objects. Topics include the following:

- Dependency Issues
- Manually Recompiling
- Listing Dependency Management Information

  See Also: f you are using Trusted Oracle, also see the volume Trusted Oracle for information about handling dependencies among schema objects in Trusted Oracle.

## 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.

  See Also: For more information on these commands, see Oracle8 SQL Reference.

  You can determine the dependencies among the schema objects in your database by running the SQL script `UTLDTREE`.`SQL`.

  See Also: 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 Oracle8 Concepts. 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 stand-alone 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 stand-alone 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 Also: "Manually Recompiling" on page 15-4 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.

### Remote Dependencies

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.

  See Also: For more information, see "Remote Dependencies" on page 10-16.

## Manually Recompiling

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.

Invalid dependent objects can be identified by querying the data dictionary views `USER_OBJECTS`, `ALL_OBJECTS`, and `DBA_OBJECTS` - see "Listing Dependency Management Information" on page 15-6 for examples.

### Manually Recompiling Views

To recompile a view, use the `ALTER` `VIEW` command with the `COMPILE` parameter. The following statement recompiles the view `EMP_DEPT` contained in your schema:

    ALTER VIEW emp_dept COMPILE;

#### Privileges Required to Recompile a View

To manually recompile a view, the view must be contained in your schema or you must have the `ALTER` `ANY` `TABLE` system privilege.

### Manually Recompiling Procedures and Functions

To recompile a procedure or function (stand-alone), use the `ALTER` `PROCEDURE` or `ALTER` `FUNCTION` command with the `COMPILE` clause. For example, the following statement recompiles the stored procedure `UPDATE_SALARY` contained in your schema:

    ALTER PROCEDURE update_salary COMPILE;

### Manually Recompiling Packages

To recompile either a package body or both a package specification and body, use the `ALTER` `PACKAGE` command with the `COMPILE` parameter. For example, the following SQL*Plus statements recompile just the body and the body and specification of the package `ACCT_MGMT_PACKAGE`, respectively:

    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;

#### Privileges Required to Recompile a Procedure or Package

You can manually recompile a procedure or package only if it is contained in your schema and you have the `ALTER` `ANY` `PROCEDURE` system privilege.

### Manually Recompiling Triggers

An existing trigger, enabled or disabled, can be manually recompiled using the `ALTER` `TRIGGER` command. For example, to force the compilation of the trigger named `REORDER`, enter the following statement:

    ALTER TRIGGER reorder COMPILE;

#### Privileges Required to Recompile a Trigger

To recompile a trigger, you must own the trigger or have the `ALTER` `ANY` `TRIGGER` system privilege.

## Listing Dependency Management Information

The following data dictionary views list information about direct dependencies and dependency management:

- `USER_DEPENDENCIES`, `ALL_DEPENDENCIES`, `DBA_DEPENDENCIES`
- `USER_OBJECTS`, `ALL_OBJECTS`, `DBA_OBJECTS`

  See Also: For a complete description of these data dictionary views, see Oracle8 Reference.

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

### The Dependency Tracking Utility

The *_`DEPENDENCIES` data dictionary views provide information about only the direct dependencies of objects. As a supplement, you can use a special dependency tracking utility to list both direct and indirect dependents of an object.

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:

##### Table DEPTREE_TEMPTAB

A temporary table used to store dependency information returned by the `DEPTREE_FILL` procedure.

Structure: | o`bject_id NUMBER` `referenced_object_id NUMBER` `nest_level NUMBER` `seq# NUMBER`

##### View DEPTREE

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 names: | n`ested_level, object_type, owner, object_name, seq#`

##### View IDEPTREE

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.

Column name: | `dependencies`

##### Sequence DEPTREE_SEQ

A sequence used to uniquely identify sets of dependency information stored in the `DEPTREE_TEMPTAB`.

##### Procedure DEPTREE_FILL

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.

Syntax: | `DEPTREE_FILL(object_type CHAR, object_owner CHAR, object_name CHAR)`

#### Using UTLDTREE While Connected as INTERNAL

If you run the `UTLDTREE`.`SQL` script and use the utility while connected as `INTERNAL`, dependency information is gathered and displayed not only for dependent objects, but also for dependent cursors (shared SQL areas).

##### Example

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 Enterprise 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"

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index