| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Invalid PL/SQL packages
Keith Finnett wrote:
>
> Is there any reason why a package created in PL/SQL would become invalid?
>
> I understand that if you are running a package from an Oracle Application
> the package is validated on startup, but sometimes the package will become
> invalid for no obvious reason.
>
> Any help would be appreciated. Please post and reply to keppy_at_lineone.net.
>
> Regards
>
> k
Please find Oracle Documentation in the following regarding your problem and a script in SQL*Plus that I wrote for this situation.
Timestamps and Signatures
In Oracle7 release 7.2 and earlier, dependencies among PL/SQL library units (packages, stored procedures, and stored functions) were handled in a very consistent, but restrictive, manner. Each time that a library unit or a relevant database object was altered, all dependent units were marked as invalid. Invalid dependent library units then had to be recompiled before they could be executed.
Timestamps
In the release 7.2 dependency model, each library unit carries a timestamp. The timestamp 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, 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. So if the procedure P3 is altered and recompiled, 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 server in a distributed environment, the timestamp information is used to mark them as invalid at runtime.
Disadvantages of the Timestamp Model
The disadvantage of this dependency model is that 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, since 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. First of all, 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 the server procedure is changed or automatically recompiled, 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.
Signatures
To alleviate some of the problems with the timestamp-only dependency model, Oracle7 release 7.3 (with PL/SQL release 2.3) introduces 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.
The signature of a subprogram contains information about the
name of the subprogram
base types of the parameters of the subprogram
modes of the parameters (IN, OUT, IN OUT)
Note: Only the types and modes of parameters are significant. The name of the parameter does not affect the signature.
The user has control over whether signatures or timestamps govern remote dependencies. See the section ``Controlling Remote Dependencies'' for more information. If the signature dependency model is in effect, a dependency on a remote library unit causes an invalidation of the dependent unit if the dependent unit contains a call to a subprogram in the parent unit, and the signature of this subprogram has been changed in an incompatible manner.
For example, consider a procedure GET_EMP_NAME stored on a server
BOSTON_SERVER. The procedure is defined
as
CREATE OR REPLACE PROCEDURE get_emp_name (
emp_number IN NUMBER,
hire_date OUT VARCHAR2,
emp_name OUT VARCHAR2) AS
BEGIN
INTO emp_name, hire_date
FROM emp
WHERE empno = emp_number;
Now assume that on another server, in California, some PL/SQL code calls GET_EMP_NAME identifying it using a DB link 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_at_BOSTON_SERVER(
emp_number, hire_date, ename);
dbms_output.put_line(ename);
dbms_output.put_line(hiredate);
END;
When this California server code is compiled, the following actions take
place:
a connection is made to the Boston server
the signature of GET_EMP_NAME is transferred to the California server
the signature is recorded in the compiled state of PRINT_ENAME
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 across to the Boston server.,
regardless of whether there were any changes or not.
If the timestamp dependency mode is in effect, a mismatch in timestamps causes an error status to be returned to the calling procedure.
However, if the signature mode is in effect, 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, the call succeeds. If they do not match, 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, due to, for example, 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.
What is a Signature?
A signature is associated with each compiled stored library unit. It identifies the unit using the following criteria:
the name of the unit, that is, the package, procedure or function name
the types of each of the parameters of the subprogram
the modes of the parameters
the number of parameters
the type of the return value for a function
When Does a Signature Change?
Datatypes: A signature changes when you change 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. Table 7 - 2 shows the classes of types.
Varchar Types:
Number Types:
VARCHAR2
NUMBER
VARCHAR
INTEGER
STRING
INT
LONG
SMALLINT
ROWID
DECIMAL
Character Types:
DEC
CHARACTER
REAL
CHAR
FLOAT
Raw Types:
NUMERIC
RAW
DOUBLE PRECISION
Date Type:
BINARY_INTEGER
DATE
MLS Label Type:
NATURAL
MLSLABEL
Modes: Changing to or from an explicit specification of the default parameter mode IN does not change the signature of a subprogram. For example, changing
PROCEDURE P1 (param1 NUMBER);
to
PROCEDURE P1 (param1 IN NUMBER);
does not change the signature. Any other change of parameter mode does change the signature.
Default Parameter Values: 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.
Examples
In the GET_EMP_NAME procedure defined , if the procedure body is changed to
BEGIN
-- date format model changes
SELECT ename, to_char(hiredate, 'DD/MON/YYYY')
INTO emp_name, hire_date
FROM emp
WHERE empno = emp_number;
But if the procedure specification is changed to
CREATE OR REPLACE PROCEDURE get_emp_name (
emp_number IN NUMBER,
hire_date OUT DATE,
emp_name OUT VARCHAR2) AS
and 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, then 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
WHERE empno = emp_data.emp_number;
END;
If the package specification is changed so that the record's field names
are changed, but the types remain the same, 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;
Controlling Remote Dependencies
Whether the timestamp or the signature dependency model is in effect is controlled by the dynamic initialization parameter REMOTE_DEPENDENCIES_MODE. If the initialization parameter file contains the specification
REMOTE_DEPENDENCIES_MODE = TIMESTAMP and this is not explicitly overridden dynamically, then only timestamps are used to resolve dependencies. This is identical to the Oracle7 Server release 7.2 model.
If the initialization parameter file contains the parameter specification
REMOTE_DEPENDENCIES_MODE = SIGNATURE and this not explicitly overridden dynamically, then signatures are used to resolve dependencies.
You can alter the mode dynamically by using the DDL commands
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = {SIGNATURE | TIMESTAMP} to alter the dependency model for the current session, or
ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = {SIGNATURE | TIMESTAMP} to alter the dependency model on a system-wide basis after startup.
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 commands, TIMESTAMP is the default
value. So, unless you explicitly use
the REMOTE_DEPENDENCIES_MODE parameter, or the appropriate DDL command,
your server is operating using the
release 7.2 timestamp dependency model.
When you use REMOTE_DEPENDENCIES_MODE=SIGNATURE you should be aware of the following:
If you change the default value of a parameter of a remote procedure, the local procedure calling the remote procedure
is not invalidated. If the call to the remote procedure does not supply the parameter, the default value is used. In this
case, because invalidation/recompilation does not automatically occur, the old default value is used. If you wish to see
the new default values, 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) , local
procedures that call the remote procedure are not invalidated. If it turns out that this overloading ought to result in a
rebinding of existing calls from the local procedure under the TIMESTAMP mode, this rebinding does not happen
under the 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, the local calling procedure is not invalidated/recompiled automatically. You must recompile the calling
procedure manually to get the semantics of the new type.
Dependency Resolution
When REMOTE_DEPENDENCIES_MODE = TIMESTAMP (the default value),
dependencies among library units are
handled exactly like in Oracle7 release 7.2 or earlier. If at runtime
the timestamp of a called remote procedure does not match
the timestamp of the called procedure, the calling (dependent) unit is
invalidated, and must be recompiled. In this case, if there
is no local PL/SQL compiler, the calling application cannot proceed.
In the timestamp dependency mode, signatures are not compared. If there is a local PL/SQL compiler, recompilation happens automatically when the calling procedure is executed.
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 normally. 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 ``What is a Signature'' ,
then an error is returned to the calling session.
Suggestions for Managing Dependencies
Oracle recommends that you 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 7.2 behavior.
Server-side PL/SQL users can choose to use the signature dependency mode if they have a distributed system and wish
to avoid possible unnecessary recompilations.
Client-side PL/SQL users should 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 timestamp mismatches.
When using SIGNATURE mode on the server side, make sure to add new procedures to the end of the procedure (or
function) declarations in a package spec. Adding a new procedure in the middle of the list of declarations can cause
unnecessary invalidation and recompilation of dependent procedures.
My Script:
set head off
set pagesize 0
set echo off
set verify off
set feedback off
set termout off
spool /tmp/recomp_ora_objects.sql
SELECT 'ALTER '||
decode(object_type, 'PACKAGE BODY', 'PACKAGE',
object_type) || ' ' ||
owner||'.'||
object_name||' compile' ||
decode(object_type, 'PACKAGE BODY', ' BODY') || ';'
FROM dba_objects
WHERE status = 'INVALID'
and object_type in ( 'PACKAGE',
'PACKAGE BODY',
'PROCEDURE',
'VIEW',
'TRIGGER' )
spool off
set feedback on
set echo on
set termout on
spool /tmp/recomp_ora_objects.log
@/tmp/recomp_ora_objects.sql
spool off
I hope this helps
Hakan Eren Received on Thu Jun 05 1997 - 00:00:00 CDT
 |
 |