Scheduler Examples

Home

Toolkit:

Note: If your session time zone isn't explicitly set in an environment variable to an Oracle named timezone, such as 'US/Eastern', I recommend that you set it before creating a Scheduler job. By default, the job's start-date inherits its time zone from your session. Unless you've explicitly set your session's time zone to an Oracle named timezone, your jobs will be off by an hour the next time the DST switch occurs. [more]

Named schedule with an EXCLUDE schedule - Inline program and schedule (I) - Inline program and schedule (II) - Converting dba_jobs jobs to Scheduler jobs - Evaluating a job's next run date

Named schedule with an EXCLUDE schedule

3 steps in this example:

--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;

Inline program and schedule

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


Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...