Recompile all invalid stored procedures in Oracle

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;

Organize into a stored procedure

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;

Run

exec Zl_Compile_Invalid_Procedure;

If you want to see the error information of the process that cannot be re-compiled, set serverout on