OraQA

Once a Database job is defined it gets a unique job id on the current database; But what if you want to replace the job definition of the *SAME* job? What if you want to replace the same job definition in another database? The id would be different.

Here is a job definition script which is deployable:

/** DATABASE.SCHEMA.OASIS.Jobs : OASIS Core
   OBJECT_TYPE  : DBA_JOB
   OBJECT_NAME  : JOB_SAMPLE_STATS
   CREATOR      : Karl r. CSC
   CREATED      : 06.01.2006
   RUN AS       : OASIS
   CMNT         : Creates/Replaces the JOB_SAMPLE_STATS database job
                  runs 02:00 AM
*/

DECLARE
  l_Job     PLS_INTEGER;
  l_Job_Sid VARCHAR2(64) := '#OASIS_JOB_001#';
BEGIN
  FOR l_Job_Defined IN (SELECT j.Job
                          FROM Dba_Jobs j
                         WHERE j.What LIKE '%' || l_Job_Sid || '%') LOOP
    Dbms_Job.Remove(l_Job_Defined.Job);
  END LOOP;
  COMMIT;

  Dbms_Job.Submit(Job      => l_Job,
                  What     => 'BEGIN /*' || l_Job_Sid ||
                              '*/ job_sample_stats; END;',
                  INTERVAL => 'trunc(sysdate) + 1 + 2/24');
  COMMIT;
END;
/

HTH

Karl

Filed under PL/SQL. Follow any responses to this entry through this RSS 2.0 feed.