重编译定义全局变量的包引发的ora-04068错误

试验环境：linux redhat 5 64bit 2.6.18-164  oracle10g 10.2.0.1 64bit enterprise

今天在学习Oracle9i10g编程艺术：深入数据库体系结构时。进行相关实验。发现一个问题，
有一个包：runstat_pkg.里面定义了三个全局变量，并且包中的三个存储过程有调用该全局变量
1：10：00：00在Session1 第一次执行包runstat_pkg中的存储过程。成功
2：10：00：05在Session2中修改runstat_pkg的包体
3：10：00：08第二次在Session1中执行包runstat_pkg发现提示错误：ora-04068
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "HARVEY.RUNSTATS_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "HARVEY.RUNSTATS_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "HARVEY.RUNSTATS_PKG"
ORA-06512: at line 1
4：10：00：08第三次在Session1中执行包runstat_pkg发现成功
发现这个奇怪的错误后。进行了几次实验进行错误的重演以及验证，总结如下：

1：对于包中有全局变量，那么无论是编译包体，包。均会出现以上错误
2：session2第二次执行失败。第三次执行成功时。发现此时由于第一次执行包而引起的全局变量的变更已经丢失。全局变量重新置为初始值。同时发现set serveroutput on选项也被重置了。
3：如果在session2中重新修改编译包之后，此时session1再次执行包之前如果先执行
dbms_session.reset_package。然后再第二次执行包时。也是成功的。但是发现此时由于第一次执行包体的存储过程导致的全局变量的改变以及丢失。全局变量重新置为初始值同时发现set serveroutput on选项也被重置了。
4：如果在sessioni2重新编译包runstat_pkg之前。在session3中从来没有执行过包runstat_pkg.则在包编译后session3中第一次执行包时也是成功的。
5：如果包中没有全局变量。则这个错误现象不会出现

后续在网上查找。发现metalink后有关于此点的一个说明。

Explanation of "ORA-04068: existing state of packages has been discarded" and "ORA-06508 PL/SQL: could not find program unit being called" errors. [ID 418270.1]

 

Applies to:

PL/SQL - Version: 9.2.0.1 to 10.2.0.4
Information in this document applies to any platform.
***Checked for relevance on 02-Apr-2010***

Purpose

This bulletin should help explain the reasoning for an "ORA-04068: existing state of packages has been discarded" to appear along with scenarios of when it does and does NOT appear.

Scope and Application

Using any two schemas (with privileges to execute a procedure in another schema) the following scenarios can be run to better understand when this error may appear.

When stepping through the different scenarios it is very important to pay close attention to which user is executing what code.

Explanation of "ORA-04068: existing state of packages has been discarded" and "ORA-06508 PL/SQL: could not find program unit being called" errors.

The "ORA-04068: existing state of packages has been discarded" error can actually be thought of as a warning in stead of an error. What this error is doing is to let the user know, the package or procedure has been recompiled. The ensuing scenarios will illustrate why this message would be important to an end user.

For example, if User2 is executing a packaged procedure from User1's schema, normally User2 will not know when or if User1 decides to rebuild or simply recompile the packaged procedure. For the most part User2 wouldn't be concerned. But there are scenarios when User2 should be notified or rather it is important to notify User2 and this Note illustrates such a scenario.

To test the code within this bulletin, open two SQL*Plus sessions. Connect one session as User1 and another session as User2. (User1 and User2 can actually be any two schemas with proper privileges to execute a procedure in another schema.)

This bulletin illustrates different scenarios for which the end user is notified and NOT notified of recompiled procedures.

=================================================================
This first scenario illustrates when User2 is NOT notified
=================================================================

As User1 - create the following packaged procedure
----------------------------------------------------------

--This is the package Spec

SQL> CREATE OR REPLACE PACKAGE P1_PACKAGE
IS
   PROCEDURE P1_PROC;
END;
/

--This is the package Body
SQL> CREATE OR REPLACE PACKAGE BODY P1_PACKAGE
IS
PROCEDURE P1_PROC IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('Hello');
   END;
END;
/

 

As User2 - execute the procedure
----------------------------------------------------------

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC;
Hello

PL/SQL procedure successfully completed.

 

As User1 - execute the following to change the packaged procedure. This change simply changes from 'Hello' to 'Hello Test2'
----------------------------------------------------------

SQL> CREATE OR REPLACE PACKAGE BODY P1_PACKAGE
IS
PROCEDURE P1_PROC IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('Hello Test2');
   END;
END;
/

 

As User2 - execute the procedure
-------------------------------------------------------

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC;
Hello Test2

PL/SQL procedure successfully completed.

Summary:
------------
In this first scenario there was a code change, but there was no need to notify the end user.

=================================================================
This second scenario illustrates when User2 is notified
=================================================================

The main difference between this scenario and the above is that this package contains a global
variable. It is the inclusion of this global variable that makes it important to notify the user.

A global variable is a variable that will hold its value even after a packaged procedure has completed
execution. Subsequent references to this global variable will now house its new value. In fact, global variables retain their new value for the life of the session. ***But if a packaged procedure is recompiled, the global variable is reset back to its initial value.***   It is because of the resetting of the global variable that makes it important to notify the end user.

As User1 - create the following packaged procedure which now contains a global variable.
-----------------------------------------------------------------------------------------------------

-- The package SPEC now includes a global variable (g_var1)

SQL> CREATE OR REPLACE PACKAGE P1_PACKAGE
IS
   g_var1 NUMBER := 100;
   PROCEDURE P1_PROC;
END;
/

--The follow package body makes reference to the global variable.

SQL> CREATE OR REPLACE PACKAGE BODY P1_PACKAGE
IS
PROCEDURE P1_PROC IS
   BEGIN
      g_var1 := g_var1 + 1;
      DBMS_OUTPUT.PUT_LINE ('The Global Variable is ' || g_var1);
   END;
END;
/

 

As User2 - execute the procedure 4 times.   Each time the global variable increments
-------------------------------------------------------------------------------------------

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC;
The Global Variable is 101

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC;
The Global Variable is 102

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC;
The Global Variable is 103

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC;
The Global Variable is 104

PL/SQL procedure successfully completed.

As User1 - execute the following to change the packaged procedure
This change simply changes the text from 'The Global Variable is ' to 'The Global Variable = '
-----------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY P1_PACKAGE
IS
PROCEDURE P1_PROC IS
   BEGIN
      g_var1 := g_var1 + 1;
      DBMS_OUTPUT.PUT_LINE ('The Global Variable = ' || g_var1);
   END;
END;
/

 As User2 - execute the procedure 4 times
The first execution produces the expected error/warning, but subsequent executions are executed as expected. After the first time, no more errors/warnings appear.

After the error/warning is encountered, continue to execute the packaged procedure 4 times.

Each time the global variable increments as before but notice the value starts back at 101 instead of
continuing with 105. The error/warning is essentially informing the end the global variable has been reset.
----------------------------------------------------------------------

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
BEGIN USER1.P1_PACKAGE.P1_PROC; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "USER1.P1_PACKAGE" has been invalidated
ORA-04065: not executed, altered or dropped package body "USER1.P1_PACKAGE"
ORA-06508: PL/SQL: could not find program unit being called:
"USER1.P1_PACKAGE"
ORA-06512: at line 1


PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable = 101

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable = 102

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable = 103

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable = 104

PL/SQL procedure successfully completed.

 Summary:
------------
In this scenario it is important to notify User2 that the existing state of the procedure has been discarded
so the user is aware the global variable has been reset. Without this error/warning, User2 would be expecting the a value of 105 to return but instead it was reset to 101.


=================================================================
This third scenario illustrates when User2 is NOT notified
=================================================================

As User1 - execute the following to change the packaged procedure
This change simply changes the text from 'The Global Variable = ' to 'The Global Variable ->'
-----------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY P1_PACKAGE
IS
PROCEDURE P1_PROC IS
   BEGIN
      g_var1 := g_var1 + 1;
      DBMS_OUTPUT.PUT_LINE ('The Global Variable -> ' || g_var1);
   END;
END;
/

As User2 - reconnect BEFORE executing the procedure 4 times
---------------------------------------------------------------

--Start a new session by reconnecting...

SQL> CONNECT USER2/TIGER

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable -> 101

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable -> 102

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable -> 103

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable -> 104

PL/SQL procedure successfully completed.

 Summary:
------------
In this scenario it is NOT important to notify User2. Since global variables are kept only for the length of
the session, either rebuilding a procedure OR reconnecting OR basically starting a new session will cause the global variable to be set to its original value. Since the end user is the one doing the reconnecting they should be aware that the global variable will have its original value and therefore does not warrant the need for the message. In fact, notifying the end user would be redundant.

=================================================================
This fourth scenario illustrates when User1 is NOT notified
=================================================================

***This scenario does not involved User2. It only involves User1. ***

As User1 - execute the procedure 4 times
----------------------------------------------------------

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable -> 101

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable -> 102

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable -> 103

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable -> 104

PL/SQL procedure successfully completed.

As User1 - execute the following to change the packaged procedure
This change simply changes the text from 'The Global Variable -> ' to 'The Global Variable is now'
-----------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY P1_PACKAGE
IS
PROCEDURE P1_PROC IS
   BEGIN
      g_var1 := g_var1 + 1;
      DBMS_OUTPUT.PUT_LINE ('The Global Variable is now ' || g_var1);
   END;
END;
/

As User1 - execute the procedure 4 times
----------------------------------------------------------

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable is now 101

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable is now 102

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable is now 103

PL/SQL procedure successfully completed.

SQL> EXECUTE USER1.P1_PACKAGE.P1_PROC
The Global Variable is now 104

PL/SQL procedure successfully completed.

Summary:
------------
In this scenario it is NOT important to notify the user simply because this is the user that actually
recompiled the package so it doesn't make sense to notify User1 that the procedure was rebuilt as User1 is the one who rebuilt it.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/180324/viewspace-660315/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/180324/viewspace-660315/