Oracle命令批量编译无效对象

DECLARE
  V_OBJOWN  DBA_OBJECTS.OWNER%TYPE;
  V_OBJNAME DBA_OBJECTS.OBJECT_NAME%TYPE;
  V_OBJTYPE DBA_OBJECTS.OBJECT_TYPE%TYPE;
  V_NUM     NUMBER;

  CURSOR CUR IS
    SELECT T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE
      FROM DBA_OBJECTS T
     WHERE T.STATUS = 'INVALID'
       AND T.OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER');
BEGIN
  SELECT COUNT(*)
    INTO V_NUM
    FROM DBA_OBJECTS T
   WHERE T.STATUS = 'INVALID'
     AND T.OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER');
  IF V_NUM > 0 THEN
    OPEN CUR;
    LOOP
      FETCH CUR
        INTO V_OBJOWN, V_OBJNAME, V_OBJTYPE;
      EXIT WHEN CUR%NOTFOUND;
      BEGIN
        EXECUTE IMMEDIATE 'alter ' || V_OBJTYPE || ' ' || V_OBJOWN || '.' ||
                          V_OBJNAME || ' compile';
        DBMS_OUTPUT.PUT_LINE('compile ' || V_OBJOWN || '.' || V_OBJNAME ||
                             ' succeed!');
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('compile ' || V_OBJOWN || '.' || V_OBJNAME ||
                               ' failed! ' || SQLERRM);
      END;
    END LOOP;
    CLOSE CUR;
  ELSE
    DBMS_OUTPUT.PUT_LINE('No invalid objects need to be compiled');
  END IF;
END;