SQL_PLUS中
spool ExecCompProcsql
select alter procedure ||object_name|| compile; From all_objects where status = INVALID and object_type = PROCEDURE;
spool off
@ExecCompProcSql;
整理成一个存储过程
Create Or Replace Procedure Zl_Compile_Invalid_Procedure As
Strsql Varchar();
Begin
For x In (Select Object_Name From All_Objects Where Status = INVALID And Object_Type = PROCEDURE) Loop
Strsql := Alter Procedure || xObject_Name || Compile;
Begin
Execute Immediate Strsql;
Exception
When Others Then Null;
When OTHERS Then dbms_outputput_line(Sqlerrm);
End;
End Loop;
End;
执行
exec Zl_Compile_Invalid_Procedure;
如果要看到无法重编译的过程的出错信息需要执行前设置set serverout on
