【备忘】PL/SQL生成脚本编译所有无效用户对象

PL/SQL生成脚本编译所有无效用户对象

-- 方法一：
BEGIN
  FOR cur IN (
    SELECT OBJECT_TYPE, OWNER, OBJECT_NAME
    FROM dba_objects
    WHERE OBJECT_TYPE IN ('PROCEDURE', 'VIEW', 'TRIGGER', 'FUNCTION') 
    AND OWNER IN ('BILL','MC','OBS','POOR','PORX','PRPA','RCMR')
    AND STATUS = 'INVALID' 
  )

  LOOP
    DBMS_OUTPUT.PUT_LINE(cur.OWNER || '.' || cur.OBJECT_NAME);
    -- ALTER VIEW MC.V_UPLOADTBCODE_OTHER COMPILE;
    EXECUTE IMMEDIATE 'ALTER ' || cur.OBJECT_TYPE || ' ' || cur.OWNER || '.' || cur.OBJECT_NAME || ' COMPILE';
  END LOOP;
END;
/

-- 方法二：
DECLARE
  c NUMBER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, 'SELECT * FROM MC.V_UPLOADTBCODE_OTHER', DBMS_SQL.NATIVE); 
  DBMS_SQL.CLOSE_CURSOR(c);
END;
/