编译oracle失效的函数、存储过程、视图等

进行oracle开发时常常为不时失效的存储过程，函数等苦恼，于是下决心解决这个问题，东查西找，找到一个好东东，在基础上又修改了一下,问题终于被我解决了。

1。建立一个存储过程

CREATE OR REPLACE PROCEDURE TIMER_AUTO_RECOMPILE_OBJS AS CURSOR OBJECTS_LIST IS SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS WHERE STATUS = 'INVALID'; BEGIN FOR V_OBJECT IN OBJECTS_LIST LOOP IF V_OBJECT.OBJECT_TYPE = 'PROCEDURE' THEN EXECUTE IMMEDIATE 'alter procedure ' || V_OBJECT.OBJECT_NAME || ' compile'; ELSEIF V_OBJECT.OBJECT_TYPE = 'FUNCTION' THEN EXECUTE IMMEDIATE 'alter function ' || V_OBJECT.OBJECT_NAME || ' compile'; ELSIF V_OBJECT.OBJECT_TYPE = 'VIEW' THEN EXECUTE IMMEDIATE 'alter view ' || V_OBJECT.OBJECT_NAME || ' compile'; ELSIF V_OBJECT.OBJECT_TYPE = 'MATERIALIZED VIEW' THEN EXECUTE IMMEDIATE 'alter materialized view ' || V_OBJECT.OBJECT_NAME || ' compile'; END IF; END LOOP; END;

 

2。在需要的时候执行，或者建立一个定时任务

exec dbms_job.submit(:job_id,'timer_auto_recompile_objs;',sysdate,'sysdate+1/24');

定时执行