[2022-02-21]调用package时提示ORA-04068报错

qxy0503

已于 2022-02-21 13:39:00 修改

阅读量1.4k

点赞数

文章标签： sql 数据库 database

于 2022-02-21 11:29:45 首次发布

本文链接：https://blog.csdn.net/m15217321304/article/details/123043613

版权

本文详细介绍了Oracle ORA-04068错误的原因，该错误通常发生在修改了PL/SQL包体后，其他会话尝试使用已缓存的过时包状态时。解决方法包括使用DBMS_SESSION.RESET_PACKAGE来重置会话中的包状态，但需要注意这可能导致全局变量的初始化。此外，文章通过示例展示了如何在不使用reset_package时保持包变量的状态，以及使用reset_package的风险和影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

业务反馈，调用package时提示ORA-04068报错，该报错在MOS（Doc ID 444968.1 Receiving ORA-04061 After Modifying A PL/SQL body）上有详细描述，主要是由于package body被修改，其他会话再次查询时已经缓存的值和编译之后的不一致，最后会提示ORA-04068,这是一个预期行为。业务想知道怎么能避免这种报错或者提前发现错误。

测试过程

session 1

1、创建包头
CREATE OR REPLACE PACKAGE packk is
      ID NUMBER(10);
      FUNCTION  FT RETURN NUMBER;
    END;
    /
2、创建包体
CREATE OR REPLACE PACKAGE BODY packk AS
      FUNCTION FT RETURN NUMBER IS
      BEGIN
        ID := 1;
        RETURN ID;
      END FT;
    END;
/


SQL> CREATE OR REPLACE PACKAGE packk is
  2        ID NUMBER(10);
  3        FUNCTION  FT RETURN NUMBER;
  4      END;
  5      /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY packk AS
  2        FUNCTION FT RETURN NUMBER IS
  3        BEGIN
  4          ID := 1;
  5          RETURN ID;
  6        END FT;
  7      END;
  8  /

Package body created.

SQL>

session 2

3、调用package FT
SQL> select packk.FT from dual;

        FT
----------
         1

SQL>

session 1

4、重新编译package FT，并修改
CREATE OR REPLACE PACKAGE BODY packk AS
      FUNCTION FT RETURN NUMBER IS
      BEGIN
        ID := 2;
        RETURN t;
      END FT;
    END;
/

--ID值由编译前的1改成2
SQL> CREATE OR REPLACE PACKAGE BODY packk AS
  2        FUNCTION FT RETURN NUMBER IS
  3        BEGIN
  4          ID := 2;
  5          RETURN FT;
  6        END FT;
  7      END;
  8  /

Package body created.

SQL>

session 2

5、再次调用FT 会报错
SQL> select packk.FT from dual;
select packk.FT from dual
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYS.PACKK" has been invalidated
ORA-04065: not executed, altered or dropped package body "SYS.PACKK"


SQL>

----//如果想解决ORA-04068，可以在调用package之前，使用dbms_session.reset_package 。

session 1 再次编译body

SQL> CREATE OR REPLACE PACKAGE BODY packk AS
  2        FUNCTION FT RETURN NUMBER IS
  3        BEGIN
  4          ID := 3;
  5          RETURN FT;
  6        END FT;
  7      END;
  8  /

Package body created.

SQL>

session 2

如果不错任何处理，直接调用package FT时，会和上面一样报错，这次调用之前先reset_package

SQL> /

        FT
----------
         2

SQL> l
  1* select packk.FT from dual
SQL> exec dbms_session.reset_package;

PL/SQL procedure successfully completed.

SQL> select packk.FT from dual

        FT
----------
         3

SQL>

但是使用reset_package存在一定风险，测试如下

--没有使用reset_package时

SQL> create or replace package pack_test is 
  2    function counter return integer; 
  3  end pack_test; 
  4  / 

Package created.

SQL> create or replace package body pack_test is 
  2    v_count integer := 0; 
  3    function counter return integer is 
  4    begin 
  5      v_count := v_count + 1; 
  6      return v_count; 
  7    end; 
  8  end pack_test; 
  9  /

Package body created.

SQL> drop table test_tab;  

Table dropped.

SQL> create table test_tab(c1 number) 
  2  /

Table created.

SQL>  declare 
  2    num number; 
  3  begin 
  4    num:= pack_test.counter; 
  5    insert into test_tab values(num); 
  6    num:= pack_test.counter; 
  7    insert into test_tab values(num); 
  8    num:= pack_test.counter; 
  9    insert into test_tab values(num); 
 10    num:= pack_test.counter; 
 11    insert into test_tab values(num); 
 12    num:= pack_test.counter; 
 13    insert into test_tab values(num); 
 14  end; 
 15  /

PL/SQL procedure successfully completed.

SQL> select * from test_tab; 

        C1
----------
         1
         2
         3
         4
         5

SQL> declare 
  2    num number; 
  3  begin 
  4    num:= pack_test.counter; 
  5    insert into test_tab values(num); 
  6    num:= pack_test.counter; 
  7    insert into test_tab values(num); 
  8    num:= pack_test.counter; 
  9    insert into test_tab values(num); 
 10  end; 
 11  / 

PL/SQL procedure successfully completed.

SQL> select * from test_tab; 

        C1
----------
         1
         2
         3
         4
         5
         6
         7
         8

8 rows selected.

SQL>

--使用reset_package时

SQL> create or replace package pack_test is 
  2    function counter return integer; 
  3  end pack_test; 
  4  / 

Package created.

SQL> create or replace package body pack_test is 
  2    v_count integer := 0; 
  3    function counter return integer is 
  4    begin 
  5      v_count := v_count + 1; 
  6      return v_count; 
  7    end; 
  8  end pack_test; 
  9  /

Package body created.

SQL> drop table test_tab purge;

Table dropped.

SQL> create table test_tab(c1 number) 
  2  /

Table created.

SQL> declare 
  2    num number; 
  3  begin 
  4    num:= pack_test.counter; 
  5    insert into test_tab values(num); 
  6    num:= pack_test.counter; 
  7    insert into test_tab values(num); 
  8    num:= pack_test.counter; 
  9    insert into test_tab values(num); 
 10    num:= pack_test.counter; 
 11    insert into test_tab values(num); 
 12    num:= pack_test.counter; 
 13    insert into test_tab values(num); 
 14  end; 
 15  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from test_tab; 

        C1
----------
         1
         2
         3
         4
         5

SQL> exec dbms_session.reset_package;

PL/SQL procedure successfully completed.

SQL> declare 
  2    num number; 
  3  begin 
  4    num:= pack_test.counter; 
  5    insert into test_tab values(num); 
  6    num:= pack_test.counter; 
  7    insert into test_tab values(num); 
  8    num:= pack_test.counter; 
  9    insert into test_tab values(num); 
 10  end; 
 11  / 

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from test_tab; 

        C1
----------
         1
         2
         3
         4
         5
         1
         2
         3

8 rows selected.

SQL>

可以看到使用reset_package之后，全局变量的值会重新初始化。

参考资料：

How To Initialize PL/SQL Package Variables in the Same Session using DBMS_SESSION.RESET_PACKAGE (Doc ID 421236.1)