Query user_objects for invalid package body
SQL> SQL> set pause off SQL> set heading off SQL> set feedback off SQL> set linesize 80 SQL> SQL> select 'alter package "' || object_name || '" compile body;' 2 from user_objects 3 where object_type = 'PACKAGE BODY' 4 and status = 'INVALID' 5 / alter package "EMP_DEPT_PROCS" compile body; alter package "DEBUG" compile body; alter package "PEOPLE" compile body; SQL> SQL> set heading on SQL> set feedback on SQL> SQL> select 'show errors package body ' || object_name 2 from user_objects 3 where object_type = 'PACKAGE BODY' 4 and status = 'INVALID' 5 / 'SHOWERRORSPACKAGEBODY'||OBJECT_NAME -------------------------------------------------------------------------------- show errors package body EMP_DEPT_PROCS show errors package body DEBUG show errors package body PEOPLE 3 rows selected. SQL> SQL>