Scheduler Examples

Named schedule with an EXCLUDE schedule

3 steps in this example:

• Create an outage schedule for planned downtimes
• Create a new schedule that runs M-Sa at 1 am, EXCEPT on planned downtimes
• Create a job to run using that schedule

--Create a simple outage schedule.  When warned of planned downtime, 
--simply change the schedule start & end dates
--to the start & end dates of the downtime window.
begin
dbms_scheduler.create_schedule
 (schedule_name => 'ADVANCE_OUTAGE_SCHEDULE',
  start_date=> trunc(sysdate-31),
  end_date=> trunc(sysdate-30),
  repeat_interval=> 'FREQ=DAILY',
  comments=>'Edit this schedule for future planned ADVANCE outages.');
end;

--Create  a schedule that runs M-Sat at 1 am, EXCEPT during downtime.
begin
dbms_scheduler.create_schedule
 (schedule_name => 'RUFFAS_ADVANCE_LOAD_SCHEDULE',
  start_date=> trunc(sysdate+1)+1/24,
  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=1; 
EXCLUDE=ADVANCE_OUTAGE_SCHEDULE',
  comments=>'Run at 1am all days but Sunday');
end;

--Create a job using this schedule.
begin
dbms_scheduler.create_job
 (job_name => 'RUFFAS_ADVANCE_DAILY_DOWNLOAD',
  job_type => 'STORED_PROCEDURE',
  job_action=> 'bulkload.ruffas_bg1045.get_daily_download',
  schedule_name=>'RUFFAS_ADVANCE_LOAD_SCHEDULE',
  enabled=>true,
  auto_drop=>false,
  comments=>'Refresh the gift_advice table from ADVANCE.WORLD');
end;

Create the job in a single call, using an inline program and schedule. Instead of calling a stored procedure, this job runs a small pl/sql program.

begin
dbms_scheduler.create_job
 (job_name => 'CLEAN_UP_APPT_PS_MAP',
  job_type => 'PLSQL_BLOCK',
  job_action=> 
'begin 
  delete from appt_ps_map where match_stat=''P'' and nvl(override,''N'')<>''Y''; 
  commit; 
end;',
  repeat_interval => 'FREQ=DAILY; BYHOUR=17; BYMINUTE=30',
  enabled=>true,
  auto_drop=>false,
  comments=>'Delete yesterday''s ''P'' matches from the appt_ps_map table');
end;

Same as above, but calling a stored procedure instead of an anonymous pl/sql block, and using an EXCLUDE schedule.

begin
dbms_scheduler.create_job
 (job_name => 'RUFFAS_ADVANCE_DAILY_DOWNLOAD',
  job_type => 'STORED_PROCEDURE',
  job_action=> 'bulkload.ruffas_bg1045.get_daily_download',
  start_date=> trunc(sysdate+1)+1/24,
  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=1; 
EXCLUDE=ADVANCE_OUTAGE_SCHEDULE',
  enabled=>true,
  auto_drop=>false,
  comments=>'Refresh the gift_advice table from ADVANCE.WORLD, at 1am all days but Sunday');
end;

Converting old-school jobs to Scheduler jobs

Here is a query to speed up the task of converting old-school DBA_JOBS jobs to scheduler jobs. It will generate a bare-bones draft of an anonymous pl/sql block which you can then edit to create your scheduler jobs. Be sure to break your old jobs before the new ones run!

select 
'dbms_scheduler.create_job( ' || chr(13)
|| '  job_name=>''' || substr(upper(translate(what,'.(),;','_')),1,26) ||'_JOB' 
|| ''', ' || chr(13)
|| '  job_type => ''PLSQL_BLOCK'',' || chr(13)
|| '  job_action=> ''begin ' || chr(13) || what  || chr(13) || 'end; '', ' || chr(13)
|| '  start_date => to_timestamp('''   
  || to_char(next_date,'mm/dd/yyyy hh24:mi:ss') 
  || ''', ''mm/dd/yyyy hh24:mi:ss''), ' || chr(13)
|| DECODE (interval,'null',NULL, 
                           '  repeat_interval => ''' || interval || ''', ' || chr(13) )
|| '  enabled => true, auto_drop=> false, ' || chr(13)
|| '  comments => ''Converted from job ' || job || '''' || chr(13)
|| ');' || chr(13)
from user_jobs 
where broken = 'N';

Sample output:

dbms_scheduler.create_job( 
  job_name=>'LOAD_PR_LOAD_ALL_JOB', 
  job_type => 'PLSQL_BLOCK',
  job_action=> 'begin 
Load.pr_Load_all();
end; ', 
  start_date => to_timestamp('05/05/2006 23:00:00', 'mm/dd/yyyy hh24:mi:ss'), 
  repeat_interval => 'trunc(sysdate + 1) + 23/24', 
  enabled => true, auto_drop=> false, 
  comments => 'Converted from job 775'
);

dbms_scheduler.create_job( 
  job_name=>'JUNE_FIRST_LOAD_JOB', 
  job_type => 'PLSQL_BLOCK',
  job_action=> 'begin
june_first_load(2006);
end; ', 
  start_date => to_timestamp('06/01/2006 00:00:00', 'mm/dd/yyyy hh24:mi:ss'), 
  enabled => true, auto_drop=> false, 
  comments => 'Converted from job 1971'
);

Evaluating a job's next run date based on its FREQUENCY calendar string

declare
  the_dt timestamp with time zone;
begin
dbms_scheduler.evaluate_calendar_string( 
  calendar_string => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=18; 
EXCLUDE=HRHDW_OUTAGE_SCHEDULE', 
  start_date => to_timestamp_tz('03/06/2006 18:00 -05:00','mm/dd/yyyy hh24:mi tzh:tzm'),
  return_date_after => to_timestamp_tz('05/02/2006 19:00 -04:00', 'mm/dd/yyyy hh24:mi tzh:tzm'),
  next_run_date => the_dt
  );
dbms_output.put_line (to_char(the_dt,'mm/dd/yyyy hh24:mi tzh:tzm'));
end;
/

05/04/2006 18:00 -05:00