重新编译啊: PROCEDURE RECOMPILE_ALL_OBJECTS IS lv_object_name varchar2(128); lv_exec_sql varchar2(100); cursor cur_proc_func is select object_name from user_objects where object_type in('PROCEDURE','FUNCTION') and status='INVALID'; -- Declare program variables as shown aboveBEGIN open cur_proc_func; loop --<<next_object>> fetch cur_proc_func into lv_object_name; exit when cur_proc_func%notfound; valid_sp(lv_object_name); end loop; close cur_proc_func;END; -- ProcedureProcedure VALID_SP(procedure_name IN varchar2) IS i_status all_objects.status%type; -- varchar2(7); i_type all_objects.object_type%type; -- varchar2(12); i_schema all_objects.owner%type; -- varchar2(30); i_procedure_name all_objects.object_name%type; -- varchar2(30);BEGIN select 'TZJF' into i_schema from dual; i_procedure_name := upper(procedure_name); begin select status , object_type into i_status , i_type from user_objects where object_name = i_procedure_name and object_type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY'); exception when others then return ; end; if i_status like 'VALID%' then return; else begin dbms_ddl.alter_compile(i_type,i_schema,i_procedure_name); return; end; end if;EXCEPTION WHEN others THEN return;END;-- Procedure;
PROCEDURE RECOMPILE_ALL_OBJECTS IS lv_object_name varchar2(128); lv_exec_sql varchar2(100); cursor cur_proc_func is select object_name
from user_objects
where object_type in('PROCEDURE','FUNCTION')
and status='INVALID'; -- Declare program variables as shown aboveBEGIN open cur_proc_func; loop --<<next_object>> fetch cur_proc_func into lv_object_name; exit when cur_proc_func%notfound; valid_sp(lv_object_name); end loop; close cur_proc_func;END; -- ProcedureProcedure VALID_SP(procedure_name IN varchar2)
IS i_status all_objects.status%type; -- varchar2(7); i_type all_objects.object_type%type; -- varchar2(12); i_schema all_objects.owner%type; -- varchar2(30); i_procedure_name all_objects.object_name%type; -- varchar2(30);BEGIN select 'TZJF' into i_schema from dual; i_procedure_name := upper(procedure_name); begin select status , object_type into i_status , i_type from user_objects where object_name = i_procedure_name and object_type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY'); exception when others then return ; end; if i_status like 'VALID%' then return; else begin dbms_ddl.alter_compile(i_type,i_schema,i_procedure_name); return; end; end if;EXCEPTION WHEN others THEN return;END;-- Procedure;