Auto Recompile Of Package Body By Job

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s