Dependencies among PL/SQL program units can be handled in two ways:
If timestamps 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 timestamp that is set by the server when the unit is created or recompiled. Figure 7-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 7-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 timestamp of P3. Therefore, if the procedure P3 is altered and recompiled, then the timestamp 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 timestamp information is used to mark them as invalid at runtime.
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 timestamp 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 timestamp 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 runtime 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.
To alleviate some of the problems with the timestamp-only dependency model, Oracle Database provides the additional capability of remote dependencies using signatures. The signature capability affects only remote dependencies. Local (same server) dependencies are not affected, as recompilation is always possible in this environment.
A signature is associated with each compiled stored program unit. It identifies the unit using the following criteria:
The user has control over whether signatures or timestamps govern remote dependencies.
When the 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 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:
Note:
You may need to set up data structures, similar to the following, for certain examples to work: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 signature, as well as its timestamp, 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:
At runtime, during the remote procedure call from the California server to the Boston server, the recorded 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 timestamps causes an error status to be returned to the calling procedure.
However, if the signature mode is in effect, then any mismatch in timestamps is ignored, and the recorded signature of get_emp_name in the compiled state of Print_ename on the California server is compared with the current 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.
Note that the get_emp_name procedure on the Boston server could have been changed. Or, its timestamp 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 signature remote dependency mode is in effect on the California server, a timestamp mismatch does not cause an error when get_emp_name is called.
Note:
DETERMINISTIC, PARALLEL_ENABLE, and purity information do not show in the signature mode. Optimizations based on these settings are not automatically reconsidered if a function on a remote system is redefined with different settings. This may 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.
Here is information on when a signature changes.
A signature changes when you switch from one class of datatype to another. Within each datatype class, there can be several types. Changing a parameter datatype from one type to another within a class does not cause the signature to change. Datatypes that are not listed in the following table, such as NCHAR or TIMESTAMP, are not part of any class; changing their type always causes a signature mismatch.
VARCHAR types: VARCHAR2, VARCHAR, STRING, LONG, ROWID
Character types: CHARACTER, CHAR
Raw types: RAW, LONG RAW
Integer types: BINARY_INTEGER, PLS_INTEGER, BOOLEAN, NATURAL, POSITIVE, POSITIVEN, NATURALN
Number types: NUMBER, INTEGER, INT, SMALLINT, DECIMAL, DEC, REAL, FLOAT, NUMERIC, DOUBLE PRECISION, DOUBLE PRECISION, NUMERIC
Date types: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
Changing to or from an explicit specification of the default parameter mode IN does not change the signature of a subprogram. For example, changing between:
PROCEDURE P1 (Param1 NUMBER); PROCEDURE P1 (Param1 IN NUMBER);
does not change the signature. Any other change of parameter mode does change the signature.
Changing the specification of a default parameter value does not change the signature. For example, procedure P1 has the same 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 signature-based invalidation occurs when a default parameter value assignment is changed.
Using the Get_emp_names procedure defined in "Parameters for Procedures and Functions", 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 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 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 signature does not change. Changing the name of a formal parameter does not change the 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 signature. For example, the following package specification has the same 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 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;
The dynamic initialization parameter REMOTE_DEPENDENCIES_MODE controls whether the timestamp or the signature dependency model is in effect.
REMOTE_DEPENDENCIES_MODE = TIMESTAMP
Then only timestamps are used to resolve dependencies (if this is not explicitly overridden dynamically).
REMOTE_DEPENDENCIES_MODE = SIGNATURE
Then signatures are used to resolve dependencies (if this not explicitly overridden dynamically).
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, then 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 timestamp dependency model.
When you use REMOTE_DEPENDENCIES_MODE=SIGNATURE:
When REMOTE_DEPENDENCIES_MODE = TIMESTAMP (the default value), dependencies among program units are handled by comparing timestamps at runtime. If the timestamp of a called remote procedure does not match the timestamp 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 timestamp dependency mode, 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 timestamp in the calling unit is first compared to the current timestamp in the called remote unit. If they match, then the call proceeds. If the timestamps do not match, then the signature of the called remote subprogram, as recorded in the calling subprogram, is compared with the current signature of the called subprogram. If they do not match (using the criteria described in the section "When Does a Signature Change?"), then an error is returned to the calling session.
Follow these guidelines for setting the REMOTE_DEPENDENCIES_MODE parameter:
< < Compiling PL/SQL Procedures for Native Execution
