6 Schema Object Dependencies

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.

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

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.

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 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:

CONNECT system/manager
CREATE PUBLIC DATABASE LINK boston_server USING 'inst1_alias';
CONNECT scott/tiger

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.

Topics:

• Switching Datatype Classes

• Examples of Changing Procedure Signatures

CHAR
CHARACTER

VARCHAR
VARCHAR2
STRING
LONG
ROWID

RAW
LONG RAW

BINARY_INTEGER
PLS_INTEGER
SIMPLE_INTEGER
BOOLEAN
NATURAL
NATURALN
POSITIVE
POSITIVEN

NUMBER
INT
INTEGER
SMALLINT
DEC
DECIMAL
REAL
FLOAT
NUMERIC
DOUBLE PRECISION

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

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

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

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;

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

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;

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;

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

• Suggestions for Managing Dependencies