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>