recompile invalid user objects
Select 'Alter '
||decode(OBJECT_TYPE
,'PACKAGE BODY','PACKAGE'
,decode(OBJECT_TYPE,'TYPE BODY','TYPE',OBJECT_TYPE))
||' '
||&OWNER
||'."'
||OBJECT_NAME
||'" COMPILE '
||decode(OBJECT_TYPE
,'PACKAGE BODY','BODY;'
,decode(OBJECT_TYPE,'TYPE BODY','BODY;',';'))
From USER_OBJECTS
Where STATUS = 'INVALID'
and OBJECT_TYPE not in ('JAVA CLASS','JAVA SOURCE');

vienkārši un eleganti.