create or replace procedure compile_invalid_procedures( p_owner varchar2 -- 所有者名称,即 SCHEMA ) as
--编译某个用户下的无效存储过程
str_sql varchar2(200);
begin for invalid_procedures in (select object_name from all_objects where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner)) loop str_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile'; begin execute immediate str_sql; exception --When Others Then Null; when OTHERS Then dbms_output.put_line(sqlerrm); end; end loop; end; 2.批量编译视图的存储过程
create or replace procedure compile_invalid_views( p_owner varchar2 -- 所有者名称,即 SCHEMA ) as
--编译某个用户下的无效存储过程
str_sql varchar2(200);
begin for invalid_views in (select object_name from all_objects where status = 'INVALID' and object_type = 'VIEW' and owner=upper(p_owner)) loop str_sql := 'alter view ' ||invalid_views.object_name || ' compile'; begin execute immediate str_sql; exception --When Others Then Null; when OTHERS Then dbms_output.put_line(sqlerrm); end; end loop; end;