表结构调整，相关的视图与存储过程失效如何处理？

Question

我做了两个表，然后做了测试用的视图与存储过程，都是引用了这个表的，然后修改了表的结构，发现存储过程与视图变成失效的了。虽然可以可以通过手工的方式可以重新编译视图与存储过程，不过比较麻烦，如果有多个存储过程和视图都与这个表相关，工作量可大了，而且如果视图还嵌套视图......MSSQL有一个sp_depends存储过程，可以把与这个表相关的视图或存储过程提取出来，然后可以编写一个小程序就可以把所有相关的视图和存储过程重新处理就好了，我就做有这样的小程序，如果Oracle也可以实现相同的功能就好了。请问各位高手，如何在Oracle中实现类似的功能呢？

Answer 1

对于失效的过程、视图，如果是在SQL中直接使用，会在使用时自动重新编译。但有些开发工具本身有缺陷，发现失效的对象就报错，如delphi通过TStoredproc执行存储过程就是这样会报错。
当然也可以自己写一个过程来重新编译失效的对象。如下面的过程就可以重新编译本schema下的非法过程、函数、包头、包体、视图、触发器：
create or replace procedure RECOMPILEALLINVALIDOBJECTS as
--重新编译所有非法的对象
  cursor c_invalidproc is
      select object_type,object_name
        from user_objects
        where status='INVALID'
          and object_type in ('PROCEDURE','FUNCTION','TRIGGER','PACKAGE','PACKAGE BODY','VIEW')
          order by object_type;
  s_sql varchar2(1000);
  s_objecttype user_objects.object_type%TYPE;
  s_objectname user_objects.object_name%TYPE;
  ct number(8);
  runs number(8);
begin
  dbms_output.enable(20000);
  ct := 0;
  loop
    open c_invalidproc;
    ct := ct+1;
    runs := 0;
    loop
      fetch c_invalidproc into s_objecttype,s_objectname;
      exit when c_invalidproc%notfound;
      runs := runs+1;
      if s_objecttype='PACKAGE' then
        s_sql := 'alter PACKAGE '||s_objectname||' compile PACKAGE';
      elsif s_objecttype='PACKAGE BODY' then
        s_sql := 'alter PACKAGE '||s_objectname||' compile BODY';
      else
        s_sql := 'alter '||s_objecttype||' '||s_objectname||' compile';
      end if;
      dbms_output.put('RUN "'||s_sql||'" ');
      << RECOMPILE_INVALID_OBJECT >>
      begin
        execute immediate s_sql;
        dbms_output.put_line('  SUCCESS');
        exception when others then
          dbms_output.put_line('  ERROR!!!');
      end RECOMPILE_INVALID_OBJECT;
    end loop;
    close c_invalidproc;
    exit when (runs=0 or ct>10);
  end loop;
  if runs>0 then
    dbms_output.put_line('以下对象仍然是非法的！');
    open c_invalidproc;
    loop
      fetch c_invalidproc into s_objecttype,s_objectname;
      exit when c_invalidproc%notfound;
      dbms_output.put_line(s_objecttype||' '||s_objectname);
    end loop;
    close c_invalidproc;
  end if;
end;
/

Answer 2

bobfang，你的办法似乎只能处理引用结构在两层的情况中的程序，如果引用关系超过两层，肯定解决不了这个问题吧？

Answer 3

兄弟们，我发现几个视图与对象的依赖性有关，他们是：
all_dependencies,dba_dependencies,user_dependencies解决问题的方法找到了，哪位兄弟比较有时间写一个小程序出来解决这个问题吗？程序最好尽可能精简可爱，谢谢!!