存储过程ORA-4068之错误解析！

下面用一个例子来分析错误：

SQL> conn /as sysdba
已连接。
SQL> grant create any table to u1;

授权成功。

SQL> conn u1/u1
已连接。
SQL> create table t as select * from t1;

表已创建。

SQL> create or replace procedure p_recreate as
  2   begin
  3    execute immediate 'drop table t purge';
  4    execute immediate 'create table t as select * from t1';
  5  end;
  6  /

过程已创建。

SQL> create or replace procedure p_insert as
  2   begin
  3     insert into t select * from t;
  4     commit;
  5  end;
  6  /

过程已创建。

SQL> begin
  2    p_recreate;
  3    p_insert;
  4  end;
  5  /
begin
*
第 1 行出现错误:
ORA-04068: 已丢弃程序包  的当前状态
ORA-04065: 未执行, 已变更或删除 stored procedure "U1.P_INSERT"
ORA-06508: PL/SQL: 无法找到正在调用 : "U1.P_INSERT" 的程序单元
ORA-06512: 在 line 3

上面建立了两个过程，在第一个过程p_recreate中，通过动态语句drop table t purge，然后在通过动态语句create table t as select * from t1来重建t表。第二个过程p_insert则更简单，就是根据t表的内容实现自复制。

将两个存储过程放在同一个匿名快中调用，就会出现上面的ORA-04068错误。如果单独执行两个过程，则不会报错：

SQL> exec p_recreate;

PL/SQL 过程已成功完成。

SQL> exec p_insert;

PL/SQL 过程已成功完成。

看到这个ORA-04068错误，首先想到的是p_recreate过程对表进行删除并重建表的操作，这会导致所有和这个t表相关的存储过程变为invalid状态。如下所示：

SQL> exec p_recreate;

PL/SQL 过程已成功完成。

SQL> select OBJECT_NAME,STATUS from user_objects where OBJECT_NAME like 'P_%';

OBJECT_NAME     STATUS
--------------- -------
P_RECREATE      VALID
P_INSERT        INVALID

由于t表结构在重建前面没有发生变化，因为通过简单的编译，就可以使得p_insert过程的状态变为valid。如下所示：

SQL> exec p_recreate;

PL/SQL 过程已成功完成。

SQL> alter procedure p_insert compile;

过程已更改。

SQL> select OBJECT_NAME,STATUS from user_objects where OBJECT_NAME like 'P_%';

OBJECT_NAME     STATUS
--------------- -------
P_RECREATE      VALID
P_INSERT        VALID

因此尝试在调用前重编译p_insert过程：

SQL> begin
  2    p_recreate;
  3    execute immediate 'alter procedure p_insert compile';
  4    p_insert;
  5  end;
  6  /
begin
*
第 1 行出现错误:
ORA-04068: 已丢弃程序包  的当前状态
ORA-04065: 未执行, 已变更或删除 stored procedure "U1.P_INSERT"
ORA-06508: PL/SQL: 无法找到正在调用 : "U1.P_INSERT" 的程序单元
ORA-06512: 在 line 4

错误依旧，而且有趣的是，由于错误发生在调用p_insert过程中，所以alter procedure p_insert compile命令显然已经执行完成，而且已经成功了。而这个命令的执行成功说明p_insert过程的状态已经恢复为valid状态了，但是错误仍然发生了。

SQL> begin
  2    p_recreate;
  3    execute immediate 'begin p_insert; end;';
  4  end;
  5  /

PL/SQL 过程已成功完成。

如果使用动态sql的方式调用p_insert过程，则不会报错。问题已经清楚了，但是要想说明白，还需要从头说起。

存储过程在编译时，会自动检查语法错误、权限以所有对象的依赖性。而等执行的时候，oracle则不会再进行类似的检查，而是直接运行过程，这也是存储过程拥有较高效率的原因之一。

当存储过程所依赖的对象发生了变化，oracle会自动将存储过程的状态设置为invalid，而存储过程的状态如果为invalid，则会在下次执行时尝试重新编译，如果编译通过则执行，如果编译失败则报错。

这就是上面例子中两个存储过程单独执行并不会报错的原因。虽然p_recreate过程会重建表t，并导致p_insert过程失效，但是p_insert在调用的时候会尝试重新编译，而由于t表的结构保持不变，所以编译不会报错，因此p_insert的调用就不会报错。

那么为什么两个过程放到一起执行就会报错且尝试重新编译也不起作用呢。这是由于导致p_insert失效的过程就在调用p_insert过程的匿名块中。在将匿名块提交给oracle时，oracle对里面的每个过程的状态进行了检查，由于导致p_insert失效的p_recreate过程还没有执行，因此这时所有过程的状态都是正常的。于是oracle记录了过程的状态信息并开始调用过程。在调用p_recreate过程后，由于t表被删除重建，p_insert的状态发生变化，但此时oracle对p_insert的检查已经完成，因此在尝试直接运行p_insert的代码时发现p_insert的状态已经发生变化，所以这里报错ORA-04068。

同样的道理，即使对p_insert进行了重新编译，oracle在执行时发现检查时代码已经发生了变化，当前的p_insert和调用时的p_insert已经发生了变化，因此仍然会报错，即使这时存储过程的状态已经是正常的。

而采用动态sql不会报错的原因就更容易理解了。采用动态sql语句，oracle将编译时进行的操作推迟到运行时进行，也就是说oracle会在调用p_recreate之后，在调用p_insert过程之前对p_insert进行检查并重新编译，所以采用动态sql不会报错。