Oracle Scheduling Implementing Error Checking Routines Oracle Tips by Burleson Consulting

Implementing Error Checking Routines

Proper error handling is an import part of implementing robust job scheduling.  Depending on the scheduling mechanism job failures can have differing effects.

For jobs scheduled using the dbms_job package, 16 consecutive failures will result in the job being marked as broken.  The following definition creates a job that will fail on every run.

* create_job_failure.sql

BEGIN
  DBMS_JOB.isubmit (1000,
                  'BEGIN RAISE_APPLICATION_ERROR(-20000, ''Error''); END;' ,
                  SYSDATE,
                  'SYSDATE + INTERVAL ''1'' SECOND');
  COMMIT;
END;
/

The job_failures.sql script queries the dba_jobs view allowing the progress of the job to be monitored.

*

job_failures.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

select
   job,
   broken,
   failures
from
   dba_jobs
where
   job = DECODE(UPPER('&1'), 'ALL', job, &1)
;

The output of this query is displayed below.

SQL> @job_failures.sql 1000

       JOB B   FAILURES
---------- - ----------
      1000 N         14

1 row selected.

After 16 failures, the broken flag has been set.

SQL> @job_failures.sql 1000

       JOB B   FAILURES
---------- - ----------
      1000 Y         16

1 row selected.

Once the problem with the job is rectified, it could be restarted using the broken procedure. 

SQL> exec dbms_job.broken(1000, false);

PL/SQL procedure successfully completed.

SQL> @job_failures.sql 1000

       JOB B   FAILURES
---------- - ----------
      1000 N         16

1 row selected.

Alternatively, the job could be dropped using the remove procedure.

SQL> exec dbms_job.remove(1000);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

By default, a job scheduled using the dbms_scheduler package does not have a limit on the maximum number of failures.  If this functionality is required, it can be enforced by setting the max_failures attribute of the job.  The create_10g_job_failure.sql script is the 10g equivalent of the create_job_failure.sql script.  Notice that the auto_drop parameter has been set to FALSE to prevent the job from being dropped once it is disabled.

* create_10g_job_failure.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'force_error_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN RAISE_APPLICATION_ERROR(-20000, ''Error''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=secondly;',
    end_date        => NULL,
    enabled         => TRUE,
    auto_drop       => FALSE,
    comments        => 'Job containing a forced error.');
  DBMS_SCHEDULER.set_attribute (
    name      => 'force_error_job',
    attribute => 'max_failures',
    value     => 16);
END;
/

The job_run_failures_10g.sql script queries the dba_scheduler_jobs view and can be used to monitor the progress of the 10g job.

* job_run_failures_10g.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

select
   job_name,
   enabled,
   run_count,
   max_runs,
   failure_count,
   max_failures
from
   dba_scheduler_jobs
where
   job_name = DECODE(UPPER('&1'), 'ALL', job_name, UPPER('&1'))
;

The output from this script is listed below.

SQL> @job_run_failures_10g.sql force_error_job

JOB_NAME        ENABL RUN_COUNT  MAX_RUNS FAILURE_COUNT MAX_FAILURES
--------------- ----- --------- --------- ------------- ------------
FORCE_ERROR_JOB  TRUE        14                      14           16

1 row selected.

Once the maximum number of failures has been reached, the job is disabled.  If the auto_drop parameter had not been set, the following query would return no rows, as the job would have been dropped.

SQL> @job_run_failures_10g.sql force_error_job

JOB_NAME        ENABL RUN_COUNT  MAX_RUNS FAILURE_COUNT MAX_FAILURES
--------------- ----- --------- --------- ------------- ------------
FORCE_ERROR_JOB FALSE        16                      16           16

1 row selected.

Once the problem with the job is rectified, it could be restarted using the enable procedure. 

SQL> exec dbms_scheduler.enable('force_error_job');

PL/SQL procedure successfully completed.

Alternatively, the job could be dropped using the drop_job procedure.

SQL> exec dbms_scheduler.drop_job ('force_error_job');

PL/SQL procedure successfully completed.

Although not directly related to errors, the maximum number of runs for a job can be limited by setting the max_runs parameter for a job.

* create_10g_job_max_runs.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'max_runs_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NULL; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=secondly;',
    end_date        => NULL,
    enabled         => TRUE,
    auto_drop       => FALSE,
    comments        => 'Job limiting maximum runs.');

  DBMS_SCHEDULER.set_attribute (
    name      => 'max_runs_job',
    attribute => 'max_runs',
    value     => 16);
END;
/

By monitoring the job, it can be seen that the job is disabled once it reaches its maximum number of runs.

SQL> @job_run_failures_10g.sql max_runs_job

JOB_NAME         ENABL RUN_COUNT MAX_RUNS FAILURE_COUNT MAX_FAILURES
---------------- ----- --------- -------- ------------- ------------
FORCE_ERROR_JOB  FALSE        16       16            16

1 row selected.

The examples above show that having a job fail to complete may introduce two possible problems.  The first and most obvious is that the work the job is expected to do will not complete successfully.  The second, and possibly most problematic, is that the job may cease to run in future.  The simplest way to solve this problem is to trap and handle all errors.  In its simplest form, this could be done using an exception handler like the one shown in the exception_job_proc_1.sql procedure below.

* exception_job_proc_1.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

CREATE OR REPLACE PROCEDURE exception_job_proc_1 AS

BEGIN
  -- Force an error.
  RAISE_APPLICATION_ERROR(-20000, ?Forced error in exception_job_proc_1?);
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END exception_job_proc_1;
/

This exception handler will stop the job from failing if a PL/SQL exception is raised, but it will give no indication what caused the job to fail.

SQL> EXEC exception_job_proc_1;

PL/SQL procedure successfully completed.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.