命令编译存储过程

create or replace procedure reCompile is
  cursor cur_invalid_objects is
    select object_name, object_type from user_objects where status = 'INVALID ';
  rec_columns cur_invalid_objects%rowtype;
  err_status  numeric;
begin
  dbms_output.enable(10000);
  open cur_invalid_objects;
  loop
    fetch cur_invalid_objects
      into rec_columns;
    exit when cur_invalid_objects%notfound;
    dbms_output.put_line('Recompiling   ' || rec_columns.object_type || '' ||
                         rec_columns.object_name);
    dbms_ddl.alter_compile(rec_columns.object_type, null, rec_columns.object_name);
  end loop;
  close cur_invalid_objects;
exception
  when others then
    begin
      err_status := sqlcode;
      dbms_output.put_line('Recompilation   failed   : ' || sqlerrm(err_status));
      if (cur_invalid_objects%isopen) then
        close cur_invalid_objects;
      end if;
    exception
      when others then
        null;
    end;
end reCompile;