存储过程失效原因分析与解决办法

现象：医院一个在用的存储过程 ADT_LOG_DAY 每天夜里通过计划任务执行应用程序调用，将前一天的数据进行汇总整理出第二天所需要查看的报表统计数据。存储过程经常失效，但从PL/SQL中又可以成功编译执行。

原因：存在存储过程 ADT_LOG_DAY调用的相关表，在调用前进行了表的结构改变。

解决方法：方法（1）在应用程序的脚本调用前，增加对存储过程ADT_LOG_DAY 重新编译的语句： EXECUTE IMMEDIATE 'ALTER PROCEDURE ADT_LOG_DAY  COMPILE'；

                   方法（2）建立一个存储过程，在需要的时候执行，或者建立一个定时任务 exec dbms_job.submit(:job_id,'timer_auto_recompile_objs;',sysdate,'sysdate+1/24')；定时执行。

create or replace procedure timer_auto_recompile_objs as cursor objects_list is select object_name,object_type from user_objects where status='INVALID'; begin for v_object in objects_list loop if v_object.object_type='PROCEDURE' then execute immediate 'alter procedure '||v_object.object_name||' compile'; elseif v_object.object_type='FUNCTION' then execute immediate 'alter function '||v_object.object_name||' compile'; elsif v_object.object_type='VIEW' then execute immediate 'alter view '||v_object.object_name||' compile'; elsif v_object.object_type='MATERIALIZED VIEW' then execute immediate 'alter materialized view '||v_object.object_name||' compile'; end if; end loop; end;

 

相关知识引入：

       当我们编译存储过程或函数时，该过程或函数引用的所有O r a c l e对象都将记录在数据字典中。
       该过程就依赖于这些存储的对象。我们可以看到在数据字典中显示了标志为非法的有编译错误的子程序。同样，如果一个D D L操作运行在其所相关的对象上时，存储子程序也将是非法的。

       当对象变更时，其相关的对象就会变成非法对象。
       如果所有的对象都在同一个数据库中的话，则相关的对象将会在底层对象变更的同时进入非法状态。由于数据字典在不断地跟踪对象间的相关，所以这种变化可以快速反应出来。

       为什么在远程调用下的过程看起来有所不同呢？

答案就在于数据字典并不跟踪远程相关对象。实际上，由于远程对象可能位于不同的数据库中，因此要将所有相关远程对象作废实际上是不可能的（如果远程对象处于无效期的话，数据字典可能无法对其进行访问）。
       与上不同的是，远程对象的合法性要在运行时进行检查。