在Oracle中重編譯所有無效的存儲過程

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
From:http://tw.wingwit.com/Article/program/Oracle/201311/18218.html