在Oracle中重编译所有无效的存储过程

作者:网络 来源:佚名 更新时间:2008-11-17 15:08:02 点击:

  sql_plus中,

spool execcompproc.sql
select 'alter procedure '||object_name||' compile;
' from all_objects where status = 'invalid' and object_type = 'procedure';
spool off
@execcompproc.sql;

  整理成一个存储过程

create or replace procedure zl_compile_invalid_procedure as
strsql varchar2(200);
begin
for x in (select object_name from all_objects where status = 'invalid'
and object_type = 'procedure') loop
strsql := 'alter procedure ' || x.object_name || ' compile';
begin
 execute immediate strsql;
 exception
--when others then null;  
when others then dbms_output.put_line(sqlerrm);  
end;
end loop;
end;

  执行

exec zl_compile_invalid_procedure;

  如果要看到无法重编译的过程的出错信息,需要执行前设置set serverout on