PL/SQL 批量编译oracle无效视图,程序包,过程

利用sqldeveloper4.5工具生成的plsql语句，以供参考。

1，编译无效视图，注意修改变量与对应的账号表名。

begin

end;

2，编译无效程序包，注意修改变量与对应的账号表名。

begin
  FOR cur IN (SELECT OBJECT_NAME, OBJECT_TYPE, owner
                FROM all_objects
               WHERE object_type in ('PACKAGE', 'PACKAGE BODY')
                 and owner = :OBJECT_OWNER
                 AND status = 'INVALID') LOOP
    BEGIN
      if cur.OBJECT_TYPE = 'PACKAGE BODY' then
        EXECUTE IMMEDIATE 'alter package "' || cur.owner || '"."' ||
                          cur.OBJECT_NAME || '" compile body';
      else
        EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.owner ||
                          '"."' || cur.OBJECT_NAME || '" compile';
      end if;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  end loop;
end;

3， 编译无效过程，注意修改变量与对应的账号表名。