Oracle® Database Concepts 11g Release 1 (11.1) Part Number B28318-03 |
|
|
View PDF |
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:
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 needs to 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 ...'
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 theDEPTREE
, IDEPTREE
, and utldtree.sql
scriptEvery 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 viewsUSER_OBJECTS
, ALL_OBJECTS
, and DBA_OBJECTS
do not distinguish between "Compiled with errors," "Invalid," and "Unauthorized"—they describe all of these as INVALID
.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 |
---|---|
|
Otherwise, no change. |
|
Otherwise, no change. |
Online Table Redefinition ( |
Otherwise, no change. |
|
Otherwise, no change. |
|
|
|
A dependent of the table on which the index is built becomes
|
|
|
|
Valid for other changes, including changes to the procedure or function body. |
|
Otherwise, no change. |
|
No change. |
|
All objects of |
|
All objects in database that depend on |
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:
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.
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.
To reduce invalidation of dependent objects, follow these guidelines:
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 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
.
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.
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:
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:
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.
Search the current schema for an object whose name matches the first piece.
Search for a public synonym that matches the first piece.
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.
Search the schema found at step d for a built-in function whose name matches the second piece of the object name.
If found, this means that 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.
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 detailsLocal 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 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 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.
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.
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 applicationsRemote procedure call (RPC) dependency management occurs when a local stored procedure calls a remote procedure in a distributed database system.
Topics:
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.
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.
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.
See Also:
"Controlling 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:
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 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 |
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.
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.
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;
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:
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.
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.
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.