Scheduling Oracle Job with Dynamic Trigger

Scheduling Oracle Job with Dynamic Trigger

  • Reading time:6 mins read

When I joined my current company about a year ago, my first task was to automate the data extraction process using Oracle job scheduler. To get an optimal result in term of data accuracy and delivery timeline, we had to track End-of-Day process stages and run the jobs at the End-of-Financial-Input stage.

Oracle Database provides advanced job scheduling capabilities through Oracle Scheduler. The most basic capability of a job scheduler is the ability to schedule a job to run at a particular date and time or when a particular event occurs. But in this article, we will go through how schedule oracle job using Oracle Scheduler with dynamic run time.

Let’s schedule a job that will start running 12:00 am every day and will keep on retrying every 15 minutes until a particular condition is met. To create a job, we use the CREATE_JOB procedure of the DBMS_SCHEDULER package. A basic create job procedure looks like this:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'EXTRACT_DATA_JOB',
   job_type           =>  'PLSQL_BLOCK', /*either STORED_PROCEDURE  or PLSQL_BLOCK*/
   job_action         =>  'BEGIN daily_data_ext_pl; END', /*plsql procedure or stored procedure name*/
   start_date         =>  TO_TIMESTAMP_TZ('11-AUG-2018 03.22.18.000000000 AM +01:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'),
   repeat_interval    =>  'FREQ=DAILY;INTERVAL=1', /* every day */
   end_date           =>  TO_TIMESTAMP_TZ('15-SEP-2035 01.00.00.000000000 AM +01:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'),
   job_class          =>  '"DEFAULT_JOB_CLASS"',
   comments           =>  'My first Oracle Job');
DBMS_SCHEDULER.ENABLE('EXTRACT_DATA_JOB');
COMMIT;
END;
/

We will be replacing the repeat_interval parameter with a function that returns the next time the job should run. At each execution of the job, Oracle scheduler calculates the next time the job should be triggered using the repeat_interval value. The job will become broken if the Scheduler is unable to determine the next time the job should run.

Let’s create an Oracle function that returns 15 minutes time if the condition is not met and 12:00 am the next day if the condition is satisfied:

CREATE OR REPLACE FUNCTION NEXT_RUN_TIME return date
as
runtime date;
BEGIN
EXECUTE IMMEDIATE 'select case when  (select count(*) from sttm_branch where ((end_of_input<>''F'' or eoc_stage<>''POSTEOFI_3'')) and record_stat=''O'')=0 or (select systemdate from dual)>(select trunc(sysdate) from dual) 
then trunc(sysdate)+ 1  //12:00am next day
else sysdate + 15/1440  //15 minute time
end 
from dual' into runtime;
RETURN runtime;
END;
/

The repeat_interval parameter will now be replaced with our new function:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'EXTRACT_DATA_JOB',
   job_type           =>  'PLSQL_BLOCK', 
   job_action         =>  'BEGIN daily_data_ext_pl; END', 
   start_date         =>  TO_TIMESTAMP_TZ('11-AUG-2018 03.22.18.000000000 AM +01:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'),
   repeat_interval    =>  'NEXT_RUN_TIME', /* Our Function */
   end_date           =>  TO_TIMESTAMP_TZ('15-SEP-2035 01.00.00.000000000 AM +01:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'),
   job_class          =>  '"DEFAULT_JOB_CLASS"',
   comments           =>  'My Oracle Job with Dynamic Trigger');
END;
/

Some useful SQL queries to monitor the jobs are:

SELECT * FROM ALL_SCHEDULER_JOBS;//returns all scheduled oracle jobs
SELECT * FROM ALL_SCHEDULER_JOB_RUN_DETAILS WHERE TRUNC(log_date) = TRUNC(sysdate) AND status = 'FAILED' order by log_date desc; //returns all failed jobs today
SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS; //currently running jobs

To manage Oracle Job, the following are some helpful commands:

BEGIN
  
//To enable a job:
  DBMS_SCHEDULER.ENABLE('EXTRACT_DATA_JOB');
//To disable a job:
  DBMS_SCHEDULER.DISABLE('EXTRACT_DATA_JOB');
//To manually run a job:
  DBMS_SCHEDULER.RUN_JOB('EXTRACT_DATA_JOB');
//To stop a job that is currently running:
  DBMS_SCHEDULER.STOP_JOB('EXTRACT_DATA_JOB');
//To delete a job:
  DBMS_SCHEDULER.DROP_JOB('EXTRACT_DATA_JOB');
END;
/

SQL Navigator has a good but not perfect Job Scheduler that can be used to manage Oracle Jobs. To access it Tools->Job Scheduler

This article was originally hosted on Medium

Tags: , ,

Leave a Reply