oracle提示存储过程无效,Oracle认证：Oracle中重新编译无效的存储过程,或函数、触发器...

女爷i 2023-01-18 02:20 596阅读 0赞

Oracle中的存储过程在有些情况下会变成失效状态，在PL/SQL Developer中该存储过程的图标左上角显示一把小红叉叉。比如储过程所引用的对象失效，dblink出问题啦都可能引起用到它的存储过程失效。再就我的存储过程经常会变成无效，至今原因都未查明。查询dba\_dependencies视图可以看到存储过程所引用的对象，再就在dba\_objects视图中可以看到对象的created和last\_ddl\_time时间。上面的那种无效的存储程，只要不是语法上有问题，重新编译一下又是可用的了。总不能每次发现时人工去编译的，所以要实现自动化，有以下两种方法(网上找到的所有的在Oracle中重新编译所有无效的存储过程代码排版都很混乱，所以主要是重新整理了)：1. Oracle SQL \*Plus中——用spool生成脚本文件，然后@调入执行，代码如下：spool ExecCompProc.sqlselect ’alter

procedure ’||object\_name||’ compile;’ from all\_objectswhere status = ’INVALID’ and object\_type = ’PROCEDURE’ AND wner=’UNMI’;spool off@ExecCompProc.sql;2.写成一个存储过程——让这个存储过程在某个时机执行，比如Job中，代码如下：create or replace procedure

compile\_invalid\_procedures(p\_owner varchar2 --所有者名称，即SCHEMA) as--编译某个用户下的无效存储过程str\_sql varchar2(200);beginfor invalid\_procedures in

(select object\_name from all\_objectswhere status = ’INVALID’ and

object\_type = ’PROCEDURE’ and wner=upper(p\_owner))loopstr\_sql := ’alter procedure ’ ||invalid\_procedures.object\_name

|| ’ compile’;beginexecute immediate str\_sql;exception--When Others Then Null;when OTHERS Thendbms\_output.put\_line(sqlerrm);end;end loop;end;在SQL \*Plus中执行该存储过程时，如果要看到dbms\_output.put\_line(sqlerrm)；的输出，需要执行set serverout on打开输出。这里述及的是如何重新编译存储过程，依次推及到，重新编译FUNCTION、PACKAGE、TYPE、TRIGGER等，和重建INDEX等。所不同的就是查询all\_objects时的object\_type不一样，还有要执行的alter语句不一样。object\_type有哪些可用select distinct object\_type

from all\_objects获取到；alter的语句写法参考下面：alter function function\_name compile;alter package package)name compile;alter type type\_name compile;alter index index\_name rebuild;--等等............补充，请看这里：1.其实存储过程、函数等是INVALID，只要内容无错误就不要紧，因为执行的时候会自动重新编译2.在SQL \*Plus或者PL/SQL Developer的Command Windows中用show errorsprocedureprocedure\_name或show errors function

function\_name可以查看到存储过程具体错误3.可以用Oracle提供的工具：dbms\_utility.compile\_schema(schema varchar2，compile\_all boolean

default TRUE)；来编译某个Schema下的所有PROCEDURE、FUNCTION、PACKAGE和TRIGGER.比如执行dbms\_utility.compile\_schema(’Unmi’)。