oracle数据库 编译无效存储过程

1.查找无效存储过程

SELECT OBJECT_NAME,TO_CHAR(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS'),STATUS FROM USER_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE IN('FUNCTION','PROCEDURE','PACKAGE') ;

2.重新编译

1. alter object_type object_namecompile;

上述两步总结如下

   

1. declare
   
2. v1 varchar2(500);
   
3. begin
   
4. for k in(select object_type,object_namefrom user_objects
   
5. where status='INVALID'and object_typein('FUNCTION','PROCEDURE','PACKAGE')) loop
   
6. v1:='alter '||k.object_type||' '||k.object_name||' compile';
   
7. execute immediate v1;
   
8. end loop;
   
9. end;

3.编译无效存储过程的过程

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;