6 Schema Object Dependencies

If the definition of object A references object B, then A depends on B. This chapter explains dependencies among schema objects, and how Oracle Database automatically tracks and manages these dependencies. Because of this automatic dependency management, A never uses an obsolete version of B, and you almost never have to explicitly recompile A after you change B.

Topics:

Overview of Schema Object Dependencies

Some types of schema objects can reference other objects in their definitions. For example, a view is defined by a query that references tables or other views, and the body of a subprogram can include SQL statements that reference other objects. If the definition of object A references object B, then A is a dependent object (with respect to B) and B is a referenced object (with respect to A).

The following query shows which object types in your database are dependent on other objects:

SELECT DISTINCT TYPE
  FROM DBA_DEPENDENCIES
     [ORDER BY TYPE]

The following query shows which object types in your database are referenced by other objects:

SELECT DISTINCT REFERENCED_TYPE
  FROM DBA_DEPENDENCIES
    [ORDER BY REFERENCED_TYPE]

The SQL*Plus script in Example 6-1 shows output from the preceding two queries.

Example 6-1 Displaying Dependent and Referenced Object Types

SQL> SELECT DISTINCT TYPE
  2    FROM DBA_DEPENDENCIES
  3      ORDER BY TYPE;

TYPE
------------------
DIMENSION
EVALUATION CONTXT
FUNCTION
INDEX
INDEXTYPE
JAVA CLASS
JAVA DATA
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
RULE
RULE SET
SYNONYM
TABLE
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
XML SCHEMA

22 rows selected.

SQL> SELECT DISTINCT REFERENCED_TYPE
  2    FROM DBA_DEPENDENCIES
  3      ORDER BY REFERENCED_TYPE;

REFERENCED_TYPE
------------------
EVALUATION CONTXT
FUNCTION
INDEXTYPE
JAVA CLASS
LIBRARY
NON-EXISTENT
OPERATOR
PACKAGE
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TYPE
VIEW
XML SCHEMA

15 rows selected.

SQL>

If you alter the definition of a referenced object, dependent objects might or might 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 is usable.

As an example of a schema object change that invalidates some dependents but not others, consider the two views in Example 6-2, which are based on the HR.EMPLOYEES table.

Suppose you determine that the EMAIL column in the EMPLOYEES table must be lengthened. You alter the table as follows:

ALTER TABLE employees MODIFY email VARCHAR2(100);

Because the COMMISSIONED view does not include EMAIL in its select list, it is not invalidated. However, because the SIXFIGURES view selects all columns in the table, it is invalidated.

select object_name, status from user_objects where object_type = 'VIEW';
 
OBJECT_NAME          STATUS
-------------------- -------
COMMISSIONED         VALID
SIXFIGURES           INVALID

Example 6-2 Schema Object Change that Invalidates Some Dependents

CREATE VIEW commissioned AS
SELECT first_name, last_name, commission_pct FROM employees
WHERE commission_pct > 0.00;
 
CREATE VIEW sixfigures AS
SELECT * FROM employees
WHERE salary >= 100000;

select object_name, status from user_objects where object_type = 'VIEW';
 
OBJECT_NAME          STATUS
-------------------- -------
COMMISSIONED         VALID
SIXFIGURES           VALID

A view depends on every object referenced in its query. The view in Example 6-3, oc_inventories, depends on the object type inventory_typ, the function warehouse_typ, and the tables inventories and warehouse.

Example 6-3 View that Depends on Multiple Objects

CREATE TYPE inventory_typ
  OID '82A4AF6A4CD4656DE034080020E0EE3D'
  AS OBJECT
    ( product_id          NUMBER(6)
    , warehouse           warehouse_typ
    , quantity_on_hand    NUMBER(8)
    ) ;
/
CREATE OR REPLACE VIEW oc_inventories OF inventory_typ
  WITH OBJECT OID (product_id)
  AS SELECT i.product_id,
            warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id),
            i.quantity_on_hand
     FROM inventories i, warehouses w
     WHERE i.warehouse_id=w.warehouse_id;

Notes:

  • CREATE statements automatically update all dependencies.

  • Dynamic SQL statements do not create dependencies. For example, the following statement does not create a dependency on tab1:

    EXECUTE IMMEDIATE 'SELECT * FROM tab1 ...'
    

Querying Object Dependencies

The static data dictionary views USER_DEPENDENCIES, ALL_DEPENDENCIES, and DBA_DEPENDENCIES describe dependencies between database objects.

The utldtree.sql SQL script creates the view DEPTREE, which contains information on the object dependency tree, and the view IDEPTREE, a presorted, pretty-print version of DEPTREE.

See Also:

Oracle Database Reference for more information about the DEPTREE, IDEPTREE, and utldtree.sql script

Object Status

Every database object has one of the status values described in Table 6-1.

Table 6-1 Database Object Status

Status Meaning

Valid

The object was successfully compiled, using the current definition in the data dictionary.

Compiled with errors

The most recent attempt to compile the object produced errors.

Invalid

The object is marked invalid because an object that it references has changed. (Only a dependent object can be invalid.)

Unauthorized

An access privilege on a referenced object was revoked. (Only a dependent object can be unauthorized.)


Note:

The static data dictionary views USER_OBJECTS, ALL_OBJECTS, and DBA_OBJECTS do not distinguish between "Compiled with errors," "Invalid," and "Unauthorized"—they describe all of these as INVALID.

Invalidation of Dependent Objects

If object A depends on object B, which depends on object C, then A is a direct dependent of B, B is a direct dependent of C, and A is an indirect dependent of C.

Direct dependents are invalidated only by changes to the referenced object that affect them (changes to the signature of the referenced object).

Indirect dependents can be invalidated by changes to the reference object that do not affect them: If a change to C invalidates B, it invalidates A (and all other direct and indirect dependents of B). This is called cascading invalidation.

Table 6-2 shows how objects are affected by changes to other objects on which they depend.

Table 6-2 Operations that Affect Object Status

Operation Resulting Status of Dependent Objects

ALTER TABLE table ADD column

INVALID when:

  • Dependent object (except a view) uses SELECT * on table.

  • Dependent object uses table%rowtype.

  • Dependent object performs INSERT on table without specifying column list.

  • Dependent object references table in query that contains a SQL join.

  • Dependent object references table in query that references a PL/SQL variable.

Otherwise, no change.

ALTER TABLE table {MODIFY|RENAME|DROP|SET UNUSED} column

ALTER TABLE table DROP CONSTRAINT not_null_constraint

INVALID when:

  • Dependent object directly references column.

  • Dependent object uses SELECT * on table.

  • Dependent object uses table%rowtype.

  • Dependent object performs INSERT on table without specifying column list.

Otherwise, no change.

CREATE OR REPLACE VIEW view

Online Table Redefinition (DBMS_REDEFINITION)

INVALID when column lists of new and old definitions differ, and at least one of the following is true:

  • Dependent object references column that is modified or dropped in new view or table definition.

  • Dependent object uses view%rowtype or table%rowtype.

  • Dependent object performs INSERT on view or table without specifying column list.

  • New view definition introduces new columns, and dependent object references view or table in query that contains a SQL join.

  • New view definition introduces new columns, and dependent object references view or table in query that references a PL/SQL variable.

  • Dependent object references view or table in RELIES ON clause.

Otherwise, no change.

CREATE OR REPLACE SYNONYM synonym

INVALID when:

  • New and old synonym targets differ, and one is not a table.

  • Both old and new synonym targets are tables, and the tables have different column lists or different privilege grants.

  • Both old and new synonym targets are tables, and dependent object is a view that references a column that participates in a unique index on the old target but not in a unique index on the new target.

Otherwise, no change.

RENAME {TABLE|VIEW|SEQUENCE|SYNONYM}

INVALID

DROP INDEX

A dependent of the table on which the index is built becomes INVALID when:

  • The index is a function-based index and the dependent object is a trigger.

  • The index is a unique index, the dependent object is a view, and the view references a column participating in the unique index.

DROP object

INVALID

CREATE OR REPLACE {PROCEDURE|FUNCTION}

INVALID if the call signature changes. The call signature is the parameter list (order, names, and types of parameters), return type, purityFoot 1 , determinism, parallelism, pipelining, and (if the procedure or function is implemented in C or Java) implementation properties.

Valid for other changes, including changes to the procedure or function body.

CREATE OR REPLACE PACKAGE

INVALID when:

  • Dependent object references a dropped or renamed package item.

  • Dependent object references a package procedure or function whose call signature or entry-point numberFoot 2 , changed.

    If referenced procedure or function has multiple overload candidates, dependent object is invalidated if any overload candidate's call signature or entry point number changed, or if a candidate was added or dropped.

  • Dependent object references a package cursor whose call signature, rowtype, or entry point number changed.

  • Dependent object references a package type or subtype whose definition changed.

  • Dependent object references a package variable or constant whose name, datatype, initial value, or offset number changed.

  • Package purity1 changed.

Otherwise, no change.

CREATE OR REPLACE PACKAGE BODY

No change.

REVOKE DML-object-privilegeFoot 3  ON object FROM user

All objects of user that depend on object are INVALID.Foot 4 

REVOKE DML-object-privilege3 ON object FROM PUBLIC

All objects in database that depend on object are INVALID.4


Footnote 1 Purity refers to a set of rules for preventing side effects (such as unexpected data changes) when invoking PL/SQL functions within SQL queries. Package purity refers to the purity of the code in the package initialization block.

Footnote 2 The entry-point number of a procedure or function is determined by its location in the PL/SQL package code. A procedure or function added to the end of a PL/SQL package is given a new entry-point number.

Footnote 3 DML object privileges are SELECT, INSERT, UPDATE, DELETE, and EXECUTE.

Footnote 4 Revalidation does not require recompilation. For explanation, see "Fast Revalidation of Invalid PL/SQL Objects" on page 6-9.

Topics:

Session State and Referenced Packages

Each session that references a package construct has its own instantiation of that package, including a persistent state of any public and private variables, cursors, and constants. All of a session's package instantiations, including state, can be lost if any of the session's instantiated packages are subsequently invalidated and revalidated.

Security Authorization

Oracle Database notices when a DML object or system privilege is granted to or revoked from a user or PUBLIC and automatically invalidates all the owner's dependent objects. Oracle Database invalidates the dependent objects to verify that an owner of a dependent object continues to have the necessary privileges for all referenced objects.

Guidelines for Reducing Invalidation

To reduce invalidation of dependent objects, follow these guidelines:

Add New Items to End of Package

When adding new items to a package, add them to the end of the package. This preserves the entrypoint numbers of existing top-level package items, preventing their invalidation.

For example, consider the following package:

CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
END;

Adding an item to the end of pkg1, as follows, does not invalidate dependents that reference the get_var function:

CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
  PROCEDURE set_var (v VARCHAR2);
END;

Inserting an item between the get_var function and the set_var procedure, as follows, invalidates dependents that reference the set_var function:

CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
  PROCEDURE assert_var (v VARCHAR2);
  PROCEDURE set_var (v VARCHAR2);
END;

Reference Each Table Through a View

Reference tables indirectly, using views. This enables you to do the following:

  • Add columns to the table without invalidating dependent views or dependent PL/SQL objects

  • Modify or delete columns not referenced by the view without invalidating dependent objects

The statement CREATE OR REPLACE VIEW does not invalidate an existing view or its dependents if the new ROWTYPE matches the old ROWTYPE.

Object Revalidation

An object that is not valid when it is referenced must be validated before it can be used. Validation occurs automatically when an object is referenced; it does not require explicit user action.

If an object is not valid, its status is either compiled with errors, unauthorized, or invalid. For definitions of these terms, see Table 6–1.

Topics:

Revalidation of Objects that Compiled with Errors

The compiler cannot automatically revalidate an object that compiled with errors. The compiler recompiles the object, and if it recompiles without errors, it is revalidated; otherwise, it remains invalid.

Revalidation of Unauthorized Objects

The compiler checks whether the unauthorized object has access privileges to all of its referenced objects. If so, the compiler revalidates the unauthorized object without recompiling it. If not, the compiler issues appropriate error messages.

Revalidation of Invalid SQL Objects

The SQL compiler recompiles the invalid object. If the object recompiles without errors, it is revalidated; otherwise, it remains invalid.

Revalidation of Invalid PL/SQL Objects

For an invalid PL/SQL program unit (procedure, function, or package), the PL/SQL compiler checks whether any referenced object changed in a way that affects the invalid object. If so, the compiler recompiles the invalid object. If the object recompiles without errors, it is revalidated; otherwise, it remains invalid. If not, the compiler revalidates the invalid object without recompiling it—see "Fast Revalidation of Invalid PL/SQL Objects".

Fast Revalidation of Invalid PL/SQL Objects

For an invalid PL/SQL program unit (procedure, function, or package), the PL/SQL compiler checks whether any referenced object changed in a way that affects the invalid object. If not, the compiler revalidates the invalid object without recompiling it. Fast revalidation is usually performed on objects that were invalidated due to cascading invalidation.

For example, consider the following table, package, and procedure:

CREATE TABLE tab1(n NUMBER);

CREATE OR REPLACE PACKAGE pkg1 IS
  TYPE rec1 IS tab1%ROWTYPE;  -- pkg1 depends on tab1
  PROCEDURE p(n NUMBER);
END pkg1;

CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
  pkg1.p(5);  -- proc1 depends on pkg1
END proc1;

The following statement invalidates pkg1 (which depends on tab1), and this invalidation cascades to proc1 (which depends on pkg1):

ALTER TABLE tab1 ADD(v VARCHAR2(20));

However, because the signature of pkg1.p has not changed, the PL/SQL compiler can revalidate proc1 without recompiling it.

Name Resolution in Schema Scope

Object names referenced in SQL statements have one or more pieces. Pieces are separated by periods—for example, hr.employees.department_id.

Oracle Database uses the following procedure to try to resolve an object name:

  1. Try to qualify the first piece of the object name.

    If the object name has only one piece, then that piece is the first piece. Otherwise, the first piece is the piece to the left of the leftmost period; for example, in hr.employees.department_id, hr is the first piece.

    The procedure for trying to qualify the first piece is:

    1. If the object name is a table name that appears in the FROM clause of a SELECT statement, and the object name has more than one piece, go to step d. Otherwise, go to step b.

    2. Search the current schema for an object whose name matches the first piece.

      If found, go to step 2. Otherwise, go to step c.

    3. Search for a public synonym that matches the first piece.

      If found, go to step 2. Otherwise, go to step d.

    4. Search for a schema whose name matches the first piece.

      If found, and if the object name has a second piece, go to step e. Otherwise, return an error—the object name cannot be qualified.

    5. Search the schema found at step d for a built-in function whose name matches the second piece of the object name.

      If found, the schema redefined that built-in function. The object name resolves to the original built-in function, not to the schema-defined function of the same name. Go to step 2.

      If not found, return an error—the object name cannot be qualified.

  2. A schema object has been qualified. Any remaining pieces of the object name must match a valid part of this schema object.

    For example, if the object name is hr.employees.department_id, hr is qualified as a schema. If employees is qualified as a table, department_id must correspond to a column of that table. If employees is qualified as a package, department_id must correspond to a public constant, variable, procedure, or function of that package.

Because of how Oracle Database resolves references, an object can depend on the nonexistence of other objects. This situation occurs when the dependent object uses a reference that would be interpreted differently if another object were present.

See Also:

Oracle Database Administrator's Guide for more details

Local Dependency Management

Local dependency management occurs when Oracle Database manages dependencies among the objects in a single database. For example, a statement in a procedure can reference a table in the same database.

Remote Dependency Management

Remote dependency management occurs when Oracle Database manages dependencies in distributed environments across a network. For example, an Oracle Forms trigger can depend on a schema object in the database. In a distributed database, a local view's defining query can reference a remote table.

Oracle Database also manages distributed database dependencies. For example, an Oracle Forms application might contain a trigger that references a table. The database system must account for dependencies among such objects. Oracle Database uses different mechanisms to manage remote dependencies, depending on the objects involved.

Topics:

Dependencies Among Local and Remote Database Procedures

Dependencies among stored procedures (including functions, packages, and triggers) in a distributed database system are managed using either time-stamp checking or signature checking (see "Time-Stamp Checking" and "Signature Checking").

The dynamic initialization parameter REMOTE_DEPENDENCIES_MODE determines whether time stamps or signatures govern remote dependencies.

Dependencies Among Other Remote Objects

Oracle Database does not manage dependencies among remote schema objects other than local-procedure-to-remote-procedure dependencies.

For example, assume that a local view is created and defined by a query that references a remote table. Also assume that a local procedure includes a SQL statement that references the same remote table. Later, the definition of the table is altered.

As a result, the local view and procedure are never invalidated, even if the view or procedure is used after the table is altered, and even if the view or procedure now returns errors when used. In this case, the view or procedure must be altered manually so that errors are not returned. In such cases, lack of dependency management is preferable to unnecessary recompilations of dependent objects.

Dependencies of Applications

Code in database applications can reference objects in the connected database. For example, OCI and precompiler applications can submit anonymous PL/SQL blocks. Triggers in Oracle Forms applications can reference a schema object.

Such applications are dependent on the schema objects they reference. Dependency management techniques vary, depending on the development environment. Oracle Database does not automatically track application dependencies.

See Also:

Manuals for your application development tools and your operating system for more information about managing the remote dependencies within database applications

Remote Procedure Call (RPC) Dependency Management

Remote procedure call (RPC) dependency management occurs when a local stored procedure calls a remote procedure in a distributed database system.

Topics:

Time-Stamp Checking

In the time-stamp checking dependency model, whenever a procedure is compiled or recompiled, its time stamp (the time it is created, altered, or replaced) is recorded in the data dictionary. The time stamp is a record of the time the procedure is created, altered, or replaced. Additionally, the compiled version of the procedure contains information about each remote procedure that it references, including the remote procedure's schema, package name, procedure name, and time stamp.

When a dependent procedure is used, Oracle Database compares the remote time stamps recorded at compile time with the current time stamps of the remotely referenced procedures. Depending on the result of this comparison, two situations can occur:

  • The local and remote procedures run without compilation if the time stamps match.

  • The local procedure is invalidated if any time stamps of remotely referenced procedures do not match, and an error is returned to the calling environment. Furthermore, all other local procedures that depend on the remote procedure with the new time stamp are also invalidated. For example, assume several local procedures call a remote procedure, and the remote procedure is recompiled. When one of the local procedures is run and notices the different time stamp of the remote procedure, every local procedure that depends on the remote procedure is invalidated.

Actual time stamp comparison occurs when a statement in the body of a local procedure runs a remote procedure. Only at this moment are the time stamps compared using the distributed database's communications link. Therefore, all statements in a local procedure that precede an invalid procedure call might run successfully. Statements subsequent to an invalid procedure call do not run at all. Compilation is required.

Depending on how the invalid procedure is called, DML statements run before the invalid procedure call are rolled back. For example, in the following, the UPDATE results are rolled back as the complete PL/SQL block changes are rolled back.

BEGIN
UPDATE table set ...
invalid_proc;
COMMIT;
END;

However, with the following, the UPDATE results are final. Only the PROC call is rolled back.

UPDATE table set ...
EXECUTE invalid_proc;
COMMIT;

If time stamps are used to handle dependencies among PL/SQL program units, then whenever you alter a program unit or a relevant schema object, all of its dependent units are marked as invalid and must be recompiled before they can be run.

Each program unit carries a time stamp that is set by the server when the unit is created or recompiled. Figure 6-1 demonstrates this graphically. Procedures P1 and P2 call stored procedure P3. Stored procedure P3 references table T1. In this example, each of the procedures is dependent on table T1. P3 depends upon T1 directly, while P1 and P2 depend upon T1 indirectly.

Figure 6-1 Dependency Relationships

Description of Figure 6-1 follows
Description of "Figure 6-1 Dependency Relationships"

If P3 is altered, then P1 and P2 are marked as invalid immediately, if they are on the same server as P3. The compiled states of P1 and P2 contain records of the time stamp of P3. Therefore, if the procedure P3 is altered and recompiled, then the time stamp on P3 no longer matches the value that was recorded for P3 during the compilation of P1 and P2.

If P1 and P2 are on a client system, or on another Oracle Database instance in a distributed environment, then the time stamp information is used to mark them as invalid at run time.

The disadvantage of this dependency model is that it is unnecessarily restrictive. Recompilation of dependent objects across the network are often performed when not strictly necessary, leading to performance degradation.

Furthermore, on the client side, the time stamp model can lead to situations that block an application from running at all, if the client-side application is built using PL/SQL version 2. Earlier releases of tools, such as Oracle Forms, that used PL/SQL version 1 on the client side did not use this dependency model, because PL/SQL version 1 had no support for stored procedures.

For releases of Oracle Forms that are integrated with PL/SQL version 2 on the client side, the time stamp model can present problems. For example, during the installation of the application, the application is rendered invalid unless the client-side PL/SQL procedures that it uses are recompiled at the client site. Also, if a client-side procedure depends on a server procedure, and if the server procedure is changed or automatically recompiled, then the client-side PL/SQL procedure must then be recompiled. Yet in many application environments (such as Forms run-time applications), there is no PL/SQL compiler available on the client. This blocks the application from running at all. The client application developer must then redistribute new versions of the application to all customers.

Signature Checking

Oracle Database provides the additional capability of remote dependencies using RPC signatures. The RPC signature capability affects only remote dependencies. Local dependencies are not affected, as recompilation is always possible in this environment.

The RPC signature of a procedure contains information about the following items:

  • Name of the package, procedure, or function

  • Base types of the parameters

  • Modes of the parameters (IN, OUT, and IN OUT)

    Note:

    Only the types and modes of parameters are significant. The name of the parameter does not affect the RPC signature.

If the RPC signature dependency model is in effect, a dependency on a remote program unit causes an invalidation of the dependent unit if the dependent unit contains a call to a procedure in the parent unit, and the RPC signature of this procedure has been changed in an incompatible manner. A program unit can be a package, stored procedure, stored function, or trigger.

To alleviate some of the problems with the time-stamp-only dependency model, Oracle Database provides the additional capability of remote dependencies using RPC signatures. The RPC signature capability affects only remote dependencies. Local (same server) dependencies are not affected, as recompilation is always possible in this environment.

An RPC signature is associated with each compiled stored program unit. It identifies the unit using the following criteria:

  • The name of the unit (the package, procedure, or function name).

  • The types of each of the parameters of the subprogram.

  • The modes of the parameters (IN, OUT, IN OUT).

  • The number of parameters.

  • The type of the return value for a function.

The user has control over whether RPC signatures or time stamps govern remote dependencies.

When the RPC signature dependency model is used, a dependency on a remote program unit causes an invalidation of the dependent unit if the dependent unit contains a call to a subprogram in the parent unit, and if the RPC signature of this subprogram has been changed in an incompatible manner.

For example, consider a procedure get_emp_name stored on a server in Boston (BOSTON_SERVER). The procedure is defined as the following:

CREATE OR REPLACE PROCEDURE get_emp_name (
   emp_number   IN  NUMBER,
   hire_date    OUT VARCHAR2,
   emp_name     OUT VARCHAR2) AS
BEGIN
   SELECT ename, to_char(hiredate, 'DD-MON-YY')
      INTO emp_name, hire_date
      FROM emp
      WHERE empno = emp_number;
END;

When get_emp_name is compiled on BOSTON_SERVER, its RPC signature, as well as its time stamp, is recorded.

Suppose that on another server in California, some PL/SQL code calls get_emp_name identifying it using a DBlink called BOSTON_SERVER, as follows:

CREATE OR REPLACE PROCEDURE print_ename (emp_number IN NUMBER) AS
   hire_date    VARCHAR2(12);
   ename        VARCHAR2(10);
BEGIN
   get_emp_name@BOSTON_SERVER(emp_number, hire_date, ename);
   dbms_output.put_line(ename);
   dbms_output.put_line(hire_date);
END;

When this California server code is compiled, the following actions take place:

  • A connection is made to the Boston server.

  • The RPC signature of get_emp_name is transferred to the California server.

  • The RPC signature is recorded in the compiled state of print_ename.

At run time, during the remote procedure call from the California server to the Boston server, the recorded RPC signature of get_emp_name that was saved in the compiled state of print_ename gets sent to the Boston server, regardless of whether or not there were any changes.

If the timestamp dependency mode is in effect, then a mismatch in time stamps causes an error status to be returned to the calling procedure.

However, if the RPC signature mode is in effect, then any mismatch in time stamps is ignored, and the recorded RPC signature of get_emp_name in the compiled state of Print_ename on the California server is compared with the current RPC signature of get_emp_name on the Boston server. If they match, then the call succeeds. If they do not match, then an error status is returned to the print_name procedure.

The get_emp_name procedure on the Boston server could have changed, or its time stamp could be different from that recorded in the print_name procedure on the California server, possibly due to the installation of a new release of the server. As long as the RPC signature remote dependency mode is in effect on the California server, a time stamp mismatch does not cause an error when get_emp_name is called.

Note:

DETERMINISTIC, PARALLEL_ENABLE, and purity information do not show in the RPC signature mode. Optimizations based on these settings are not automatically reconsidered if a function on a remote system is redefined with different settings. This might lead to incorrect query results when calls to the remote function occur, even indirectly, in a SQL statement, or if the remote function is used, even indirectly, in a function-based index.

Topics:

Switching Datatype Classes

A RPC signature changes when you switch from one datatype class to another. A datatype class can include several datatypes. Changing a parameter datatype to another datatype in a class does not change the RPC signature.

Table 6-3 lists the datatype classes and the datatypes that comprise them. Datatypes that are not listed in Table 6-3, such as NCHAR or TIMESTAMP, are not part of any class; changing their type always causes a RPC signature mismatch.

Table 6-3 Datatype Classes

Datatype Class Datatypes in Class

Character

CHAR
CHARACTER

VARCHAR

VARCHAR
VARCHAR2
STRING
LONG
ROWID

Raw

RAW
LONG RAW

Integer

BINARY_INTEGER
PLS_INTEGER
SIMPLE_INTEGER
BOOLEAN
NATURAL
NATURALN
POSITIVE
POSITIVEN

Number

NUMBER
INT
INTEGER
SMALLINT
DEC
DECIMAL
REAL
FLOAT
NUMERIC
DOUBLE PRECISION

Date

DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

Modes

Changing to or from an explicit specification of the default parameter mode IN does not change the RPC signature of a subprogram. For example, changing between:

PROCEDURE P1 (Param1 NUMBER);
PROCEDURE P1 (Param1 IN NUMBER);

does not change the RPC signature. Any other change of parameter mode does change the RPC signature.

Default Parameter Values

Changing the specification of a default parameter value does not change the RPC signature. For example, procedure P1 has the same RPC signature in the following two examples:

PROCEDURE P1 (Param1 IN NUMBER := 100);
PROCEDURE P1 (Param1 IN NUMBER := 200);

An application developer who requires that callers get the new default value must recompile the called procedure, but no RPC signature-based invalidation occurs when a default parameter value assignment is changed.

Examples of Changing Procedure Signatures

Using the Get_emp_names procedure shown previously in this chapter, if the procedure body is changed to the following:

DECLARE
   Emp_number  NUMBER;
   Hire_date   DATE;
BEGIN
-- date format model changes
  
   SELECT Ename, To_char(Hiredate, 'DD/MON/YYYY')
      INTO Emp_name, Hire_date
      FROM Emp_tab
      WHERE Empno = Emp_number;
END;

The specification of the procedure has not changed, so its RPC signature has not changed.

But if the procedure specification is changed to the following:

CREATE OR REPLACE PROCEDURE Get_emp_name (
   Emp_number  IN  NUMBER,
   Hire_date   OUT DATE,
   Emp_name    OUT VARCHAR2) AS

And if the body is changed accordingly, then the RPC signature changes, because the parameter Hire_date has a different datatype.

However, if the name of that parameter changes to When_hired, and the datatype remains VARCHAR2, and the mode remains OUT, the RPC signature does not change. Changing the name of a formal parameter does not change the RPC signature of the unit.

Consider the following example:


CREATE OR REPLACE PACKAGE Emp_package AS
    TYPE Emp_data_type IS RECORD (
        Emp_number NUMBER,
        Hire_date  VARCHAR2(12),
        Emp_name   VARCHAR2(10));
    PROCEDURE Get_emp_data
        (Emp_data IN OUT Emp_data_type);
END;

CREATE OR REPLACE PACKAGE BODY Emp_package AS
    PROCEDURE Get_emp_data
        (Emp_data IN OUT Emp_data_type) IS
   BEGIN
       SELECT Empno, Ename, TO_CHAR(Hiredate, 'DD/MON/YY')
           INTO Emp_data
           FROM Emp_tab
           WHERE Empno = Emp_data.Emp_number;
   END;
END;

If the package specification is changed so that the record's field names are changed, but the types remain the same, then this does not affect the RPC signature. For example, the following package specification has the same RPC signature as the previous package specification example:

CREATE OR REPLACE PACKAGE Emp_package AS
    TYPE Emp_data_type IS RECORD (
        Emp_num    NUMBER,         -- was Emp_number
        Hire_dat   VARCHAR2(12),   -- was Hire_date
        Empname    VARCHAR2(10));  -- was Emp_name
    PROCEDURE Get_emp_data
        (Emp_data IN OUT Emp_data_type);
END;

Changing the name of the type of a parameter does not cause a change in the RPC signature if the type remains the same as before. For example, the following package specification for Emp_package is the same as the first one:

CREATE OR REPLACE PACKAGE Emp_package AS
    TYPE Emp_data_record_type IS RECORD (
        Emp_number NUMBER,
        Hire_date  VARCHAR2(12),
        Emp_name   VARCHAR2(10));
    PROCEDURE Get_emp_data
        (Emp_data IN OUT Emp_data_record_type);
END;

Controlling Remote Dependencies

The dynamic initialization parameter REMOTE_DEPENDENCIES_MODE controls whether the time stamp or the RPC signature dependency model is in effect.

  • If the initialization parameter file contains the following specification:

    REMOTE_DEPENDENCIES_MODE = TIMESTAMP
    

Then only time stamps are used to resolve dependencies (if this is not explicitly overridden dynamically).

  • If the initialization parameter file contains the following parameter specification:

    REMOTE_DEPENDENCIES_MODE = SIGNATURE
    

Then RPC signatures are used to resolve dependencies (if this not explicitly overridden dynamically).

  • You can alter the mode dynamically by using the DDL statements. For example, this example alters the dependency model for the current session:

    ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = 
        {SIGNATURE | TIMESTAMP}
    
    Thise example alters the dependency model systemwide after startup:
    ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = 
        {SIGNATURE | TIMESTAMP}
    

If the REMOTE_DEPENDENCIES_MODE parameter is not specified, either in the init.ora parameter file or using the ALTER SESSION or ALTER SYSTEM DDL statements, TIMESTAMP is the default value. Therefore, unless you explicitly use the REMOTE_DEPENDENCIES_MODE parameter, or the appropriate DDL statement, your server is operating using the time-stamp dependency model.

When you use REMOTE_DEPENDENCIES_MODE=SIGNATURE:

  • If you change the default value of a parameter of a remote procedure, then the local procedure calling the remote procedure is not invalidated. If the call to the remote procedure does not supply the parameter, then the default value is used. In this case, because invalidation/recompilation does not automatically occur, the old default value is used. If you want to see the new default values, then you must recompile the calling procedure manually.

  • If you add a new overloaded procedure in a package (a new procedure with the same name as an existing one), then local procedures that call the remote procedure are not invalidated. If it turns out that this overloading results in a rebinding of existing calls from the local procedure under the time-stamp mode, then this rebinding does not happen under the RPC signature mode, because the local procedure does not get invalidated. You must recompile the local procedure manually to achieve the new rebinding.

  • If the types of parameters of an existing packaged procedure are changed so that the new types have the same shape as the old ones, then the local calling procedure is not invalidated or recompiled automatically. You must recompile the calling procedure manually to get the semantics of the new type.

Topics:

Dependency Resolution

When REMOTE_DEPENDENCIES_MODE = TIMESTAMP (the default value), dependencies among program units are handled by comparing time stamps at run time. If the time stamp of a called remote procedure does not match the time stamp of the called procedure, then the calling (dependent) unit is invalidated and must be recompiled. In this case, if there is no local PL/SQL compiler, then the calling application cannot proceed.

In the time-stamp dependency mode, RPC signatures are not compared. If there is a local PL/SQL compiler, then recompilation happens automatically when the calling procedure is run.

When REMOTE_DEPENDENCIES_MODE = SIGNATURE, the recorded time stamp in the calling unit is first compared to the current time stamp in the called remote unit. If they match, then the call proceeds. If the time stamps do not match, then the RPC signature of the called remote subprogram, as recorded in the calling subprogram, is compared with the current RPC signature of the called subprogram. If they do not match (using the criteria described in the section "Switching Datatype Classes"), then an error is returned to the calling session.

Suggestions for Managing Dependencies

Follow these guidelines for setting the REMOTE_DEPENDENCIES_MODE parameter:

  • Server-side PL/SQL users can set the parameter to TIMESTAMP (or let it default to that) to get the time-stamp dependency mode.

  • Server-side PL/SQL users can choose to use the RPC signature dependency mode if they have a distributed system and they want to avoid possible unnecessary recompilations.

  • Client-side PL/SQL users must set the parameter to SIGNATURE. This allows:

    • Installation of new applications at client sites, without the need to recompile procedures.

    • Ability to upgrade the server, without encountering time stamp mismatches.

  • When using RPC signature mode on the server side, add new procedures to the end of the procedure (or function) declarations in a package specification. Adding a new procedure in the middle of the list of declarations can cause unnecessary invalidation and recompilation of dependent procedures.

Shared SQL Dependency Management

In addition to managing dependencies among schema objects, Oracle Database also manages dependencies of each shared SQL area in the shared pool. If a table, view, synonym, or sequence is created, altered, or dropped, or a procedure or package specification is recompiled, all dependent shared SQL areas are invalidated. At a subsequent execution of the cursor that corresponds to an invalidated shared SQL area, Oracle Database reparses the SQL statement to regenerate the shared SQL area.