I have a procedure in a database that becomes invalid every night, it seems that there is a process that invalidates the procedure due that touches the definition of a table used into the procedure.
For now the solution has been to create a job (of course, should only use DBMS_SCHEDULER and foget traditional jobs) and recompile at a certain time. This script is very simple, but my case is sufficient. It could give you more intelligence and control, as it send an email if it fails.
-- CREATE THE PROCEDURE CREATE OR REPLACE PROCEDURE recompila_logis AS LINEA VARCHAR2 (50); ESTADO VARCHAR2 (7); BEGIN SELECT STATUS INTO ESTADO FROM DBA_OBJECTS WHERE OBJECT_ID = 80134; IF ESTADO <> 'VALID' THEN LINEA := 'ALTER PACKAGE LOGIS.DELIVERY_LOCAL_REGION COMPILE BODY'; EXECUTE IMMEDIATE LINEA; END IF; COMMIT; END; -- IN MY CASE ALLWAYS I HAVE DISABLE COMMIT IN PROCEDURE ALTER SESSION ENABLE COMMIT IN PROCEDURE; -- CREATE JOB DECLARE JOBNO NUMBER; BEGIN DBMS_JOB.SUBMIT(JOBNO,'recompila_logis;',TO_DATE('10/11/2014 01:00:00','DD/MM/YYYY HH24:MI:SS') ,'TRUNC(SYSDATE + 1) + 1/24'); COMMIT; END; /
HTH – Antonio NAVARRO