Recompile all invalid package : user_objects « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. user_objects

SQL>
SQL>
SQL> declare
  2      cursor myCursor is select object_type, object_name from SYS.USER_OBJECTS where  status = 'INVALID'
  3      order by 1, 2;
  4      sqlString varchar2(100);
  5  begin
  6    for r1 in myCursor loop
  7      begin
  8        if r1.object_type = 'PACKAGE BODY' then
  9
 10          sqlString := 'alter PACKAGE '||r1.object_name||' compile BODY';
 11
 12        elsif r1.object_type = 'TYPE BODY' then
 13
 14          sqlString := 'alter TYPE '||r1.object_name||' compile BODY';
 15
 16        else
 17
 18          sqlString := 'alter '||r1.object_type||' '||r1.object_name||' compile';
 19
 20        end if;
 21
 22        execute immediate sqlString;
 23
 24        dbms_output.put_line(r1.object_type||' '||r1.object_name||' compiled successfully');
 25
 26      exception
 27
 28        when OTHERS then
 29
 30          dbms_output.put_line(SQLERRM||' on '||sqlString);
 31
 32      end;
 33
 34    end loop;
 35
 36  end;
 37  /

PL/SQL procedure successfully completed.

SQL>