CODEWITHSUNDEEP
oracle-sqldeveloperjobsschedule
Paweł
Paweł

Reputation: 11

How to schedule a job in SQL Developer

I would like to schedule a procedure to run at specificic hours, three times a day: at 12:00, 16:00 and 18:30. Is there any way to create such a job (using standard SQL Developer job wizard)?

I scheduled a job, by setting BYHOUR = 12,16,18 and BYMINUTE = 0,30, but then it starts six times a day, which is not exactly what I want.

Thanks in advance for tips!

Upvotes: 1

Views: 15167

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

The simplest way, if you don't want to create two jobs, is to create two schedules instead. You can do this through the Schedules->New Schedule context menu item, or from a worksheet:

begin
  dbms_scheduler.create_schedule(schedule_name => 'sched_1',
    repeat_interval => 'FREQ=MINUTELY;BYHOUR=12,16;BYMINUTE=0');
  dbms_scheduler.create_schedule('sched_2',
    repeat_interval => 'FREQ=MINUTELY;BYHOUR=18;BYMINUTE=30');
end;
/

Then in the job wizard, set 'Repeat Interval' to SCHED_1,SCHED_2. Of course, you might want to use more meaningful names...

You can check when the combined schedule will run - after the current time - with something like this:

set serveroutput on;
declare
  start_date timestamp with time zone;
  return_date_after timestamp with time zone;
  next_run_date timestamp with time zone;
begin
  start_date := cast(sysdate as timestamp with time zone);

  for i in 1 .. 8 loop
    return_date_after := nvl(next_run_date, start_date);

    dbms_scheduler.evaluate_calendar_string(
      calendar_string => 'sched_1,sched_2',
      start_date => start_date,
      return_date_after => return_date_after,
      next_run_date => next_run_date);

    dbms_output.put_line('Will run at: '
      || to_char(next_run_date, 'YYYY-MM-DD HH24:MI:SS'));
  end loop;
end;
/

Will run at: 2014-04-08 16:00:18
Will run at: 2014-04-08 18:30:18
Will run at: 2014-04-09 12:00:18
Will run at: 2014-04-09 16:00:18
Will run at: 2014-04-09 18:30:18
Will run at: 2014-04-10 12:00:18
Will run at: 2014-04-10 16:00:18
Will run at: 2014-04-10 18:30:18

Upvotes: 2

Related Questions