修改表结构,相关视图、函数和存储过程失效
例如:
将表中的字段类型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
且只能处理引用结构在两层的情况中的程序
与你共亲到无可亲密时,便知友谊万岁是尽头。