修改表结构,相关视图、函数和存储过程失效

例如:

  将表中的字段类型NUMBER(19,2)修改成NUMBER(19,4),这时相关的视图、函数和存储过程会失效,导致无法引用

解决方案:

1、直接找到相应的视图、函数或存储过程,重新编译。

2、通过sql查询相关的视图、函数或存储过程,批量编译

select 'ALTER ' || OBJECT_TYPE || ' ' || owner || '.' || OBJECT_NAME ||  
       ' COMPILE;'  
  from all_objects  
where status = 'INVALID'  
   and object_type in ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TABLE', 'VIEW',  
        'SEQUENCE', 'TRIGGER');  
select 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE body;'  
  from ALL_objects  
where status = 'INVALID'  
   and object_type in ('PACKAGE BODY');  
----------将查询出来的结果执行下就可以了。。

3、使用存储过程重新编译非法过程、函数、包头、包体、视图、触发器:

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;

  注:以上程序源自:http://www.dataguru.cn/thread-139510-1-1.html

且只能处理引用结构在两层的情况中的程序

  

 

posted @ 2017-01-06 15:47  *ち黑サカ  阅读(997)  评论(0编辑  收藏  举报