-- Compiling invalid objects by 'ALTER ' command on an ad-hoc basis.


DECLARE
 CURSOR c_inv_obj IS
  SELECT owner, object_name, object_type
  FROM  all_objects
  WHERE  object_type IN ('FUNCTION','PROCEDURE',
                         'PACKAGE','PACKAGE BODY')
  AND   status = 'INVALID'
  AND   owner = user;

BEGIN
 FOR inv_rec IN c_inv_obj LOOP
   BEGIN
     IF  inv_rec.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'ALTER PACKAGE '||inv_rec.owner||'.'||
          inv_rec.inv_rec.object_name ||' COMPILE BODY';
     ELSE
        EXECUTE IMMEDIATE 'ALTER '||inv_rec.object_type||' '||
          inv_rec.owner||'.'||inv_rec.inv_rec.object_name ||' COMPILE'; 
     END IF;
   EXCEPTION
     WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error in compilation: '||SQLERRM);
      RAISE;
   END;
 END LOOP;
END;
/