How to set start_date and end_date on a dbms job (oracle)

Question

I'm creating a scheduler for my oracle DB This is what i got so far :

BEGIN 
DBMS_SCHEDULER.CREATE_JOB (
job_name            => 'CREAZIONE_OCCORRENZE',
job_type            => 'STORED_PROCEDURE',
job_action          => 'pop_occr_lezione'
start_date          =>  A,
end_date            =>  B,
repeat_interval     => 'FREQ=WEEKLY'
enabled             => true,
auto_drop           => false;
)
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name            => 'ASSEGNAZIONE - AULE',
job_type            => 'STORED_PROCEDURE',
job_action          => 'ass_aule'
start_date          =>  C,
end_date            =>  D,
repeat_interval     => 'FREQ=WEEKLY'
enabled             => true,
auto_drop           => false;
)
END;
/

As you see i still need to set start_date and end_date for the 2 jobs. Wich is rather confusing, how can i set :

A = the last monday of august ( valid for each year )

B = the first monday of august ( a year later of A )

C = the first sunday after A

D = the first sunday after B

how can i do such a thing ?

For me the actual interval is not clear. When do you like it to run? — Wernfried Domscheit, Mar 23 '15 at 19:30
the first job should run the first monday before the first of september ( as it generates the dates of the next week in wich it's executed. The second should start the sunday of the week that the first started, since it do some calculation on the dates created by the first. After that each monday the first should be fired and each sunday the second. the last run of the first should be in july, in the second last monday, so that the second can operate his sunday calculation for the week after ( wich will be the last week of july) — darkpirate, Mar 23 '15 at 19:46
`start_date` does **not** mean "when does the job start", it is used to determine the first execution date of the job based on `start_date` and `repeat_interval`. Also when current date pass `end_date`, then the job will never run again. Locks more you have to define four jobs in total (or at least four `SCHEDULES`) — Wernfried Domscheit, Mar 23 '15 at 20:34
can you explain further your point of view ? why 4 jobs ? what each job do ? — darkpirate, Mar 23 '15 at 21:00
I still don't get the meaning of your interval. Does it mean, it may change every year (depending on when is the last Monday of August)? You give us four intervals (or dates) for two jobs, this does not match. — Wernfried Domscheit, Mar 23 '15 at 21:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/73617/discussion-between-darkpirate-and-wernfried). — darkpirate, Mar 23 '15 at 21:18
let's do a practical example : the first one is fired the last monday of august 25-aug-2014 the second one is fired that sunday 31-august-2014 they repeat weekly ( so each monday-sunday after ) then the first one has to be executed for the last time the first monday of july 06-07-2015 and the second the first monday of july 12-07-2015 — darkpirate, Mar 23 '15 at 21:29
i just wanted to avoid hard coded dates, and since it would be good that every year those jobs are activated/deactivated again, i was wondering if there was a way.. — darkpirate, Mar 23 '15 at 21:36

Wernfried Domscheit · Answer 1 · 2015-03-24T08:38:22.527

start_date and end_date are fixed values, i.e. you cannot say "the last Monday of August ( valid for each year )". start_date is only used for the initial value of repeat_interval.

For example start_date => TIMESTAMP '2015-03-26 18:00:00', repeat_interval => 'FREQ=WEEKLY' means every Monday at 18:00:00.

end_date is the date when your job becomes disabled.

repeat_interval for "every last Monday of August" would be FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=-1 MON

repeat_interval for "every first Sunday of August" would be FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=1 SUN

You can verify with this procedure:

DECLARE
    next_run_date TIMESTAMP WITH TIME ZONE;
BEGIN
    FOR i IN 1..10 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=-1 MON', NULL, next_run_date, next_run_date);
        DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd fmDay') );
    END LOOP;

    next_run_date := NULL;
    FOR i IN 1..10 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=1 SUN', NULL, next_run_date, next_run_date);
        DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd fmDay') );
    END LOOP;

END;

2015-08-31 Monday
2016-08-29 Monday
2017-08-28 Monday
2018-08-27 Monday
2019-08-26 Monday
2020-08-31 Monday
2021-08-30 Monday
2022-08-29 Monday
2023-08-28 Monday
2024-08-26 Monday

2015-08-30 Sunday
2016-08-28 Sunday
2017-08-27 Sunday
2018-08-26 Sunday
2019-08-25 Sunday
2020-08-30 Sunday
2021-08-29 Sunday
2022-08-28 Sunday
2023-08-27 Sunday
2024-08-25 Sunday

Check Calendaring Syntax for further details

Based on this you can create another job which set the start_date of main jobs, i.e.:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            => 'SET_START_TIME',
   job_type            => 'PLSQL_BLOCK',
   job_action          => 'BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE(''CREAZIONE_OCCORRENZE'', ''START_DATE'', LOCALTIMESTAMP); END;',
   repeat_interval     => 'FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=-1 MON'
   enabled             => TRUE,
   auto_drop           => FALSE);
END;

hum... so i should use a defined date... But this means that everytime you want it to stop and resume you have to define a new job ? seems clunky. My goal is to produce a job that activate in a certain date, deactivate in another date every year, how could achieve this ? maybe nested jobs ? is that even possible ? xD — darkpirate, Mar 23 '15 at 18:58
if i set a fixed date as start_day, prior to the system date, will it run anyways or it has to be post dated in order to work ? — darkpirate, Mar 23 '15 at 19:14

score 0 · Answer 2 · answered Mar 23 '15 at 18:37

You can use this query to get the info ... then you just have to stuff the values into your dbms_scheduler. If you need to do this every year, you may need to use dynamic sql to call the scheduler.

  with w_current as (
        select add_months(trunc(sysdate,'YYYY'),7+12) aug1,
               last_day(add_months(trunc(sysdate,'YYYY'),7)) aug31
          from dual
        ),
     w_mondays as (
           select  aug1+mod(9-to_char(aug1,'D'),7) first_mon_aug,
                   aug31+mod(8-to_char(aug31,'D'),7)-6  last_mon_aug
             from w_current
        )
  select first_mon_aug, last_mon_aug,
         first_mon_aug+6   first_sun_after_A,
         last_mon_aug+6    first_sun_after_B
    from w_mondays;

Not really sure what you're asking .. did you see Wernfried's answer? Use it ... probably more correct than mine :) (I hadn't used dbms scheduler for a bit and forgot what he mentioned in his answer) :) — Ditto, Mar 23 '15 at 18:49

score 0 · Answer 3 · answered Mar 24 '15 at 07:47

I am confused, you are mixing Monday and Sunday (2015-07-12 is Monday, not Sunday) and you changed the requirements in your question.

As far as I understand your schedule and in order to simplify, you like to run job CREAZIONE_OCCORRENZE every Monday except from "2nd Monday in July till last but one Monday in August". Job ASSEGNAZIONE_AULE runs always five day after.

Then you could work with exclusions. It should be this one:

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name => 'MONDAY_AUGUST',
   repeat_interval => 'FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=-5 MON,-4 MON,-3 MON,-2 MON');

DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name => 'MONDAY_JULY',
   repeat_interval => 'FREQ=MONTHLY;INTERVAL=1;BYMONTH=JUL;BYDAY=2 MON,3 MON,4 MON,5 MON');

DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name => 'MONDAYS',
   repeat_interval => 'FREQ=WEEKLY;INTERVAL=1;BYDAY=MON;EXCLUDE=MONDAY_AUGUST,MONDAY_JULY');

DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name => 'SUNDAYS',
   repeat_interval => 'MONDAYS+OFFSET:6D');
END;


BEGIN 
DBMS_SCHEDULER.CREATE_JOB (
   job_name            => 'CREAZIONE_OCCORRENZE',
   job_type            => 'STORED_PROCEDURE',
   job_action          => 'pop_occr_lezione'
   start_date          =>  NULL,
   end_date            =>  NULL,
   repeat_interval     => 'MONDAYS'
   enabled             => true,
   auto_drop           => false);

DBMS_SCHEDULER.CREATE_JOB (
   job_name            => 'ASSEGNAZIONE - AULE',
   job_type            => 'STORED_PROCEDURE',
   job_action          => 'ass_aule'
   start_date          =>  NULL,
   end_date            =>  NULL,
   repeat_interval     => 'SUNDAYS'
   enabled             => true,
   auto_drop           => false);
END;
/

And the PL/SQL Block to test the schedule:

DECLARE
    next_run_date TIMESTAMP WITH TIME ZONE;
BEGIN

    next_run_date := NULL;  
    DBMS_OUTPUT.PUT_LINE ('Excluded Mondays in August' );
    FOR i IN 1..10 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('MONDAY_AUGUST', NULL, next_run_date, next_run_date);
        DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd wTH fmDay') );
    END LOOP;

    next_run_date := NULL;  
    DBMS_OUTPUT.PUT_LINE ('Excluded Mondays in July' );
    FOR i IN 1..10 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('MONDAY_JULY', NULL, next_run_date, next_run_date);
        DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd wTH fmDay') );
    END LOOP;

    next_run_date := NULL;  
    DBMS_OUTPUT.PUT_LINE ('Executions Job 1' );
    FOR i IN 1..150 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('MONDAYS', NULL, next_run_date, next_run_date);
        IF TO_CHAR(next_run_date, 'IW') BETWEEN 25 AND 38 THEN -- avoid excessive output
            DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd wTH fmDay') );
        END IF;
    END LOOP;

    next_run_date := NULL;
    DBMS_OUTPUT.PUT_LINE ('Executions Job 2' );
    FOR i IN 1..150 LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('SUNDAYS', NULL, next_run_date, next_run_date);
        IF TO_CHAR(next_run_date, 'IW') BETWEEN 25 AND 38 THEN -- avoid excessive output
            DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd wTH fmDay') );
        END IF;
    END LOOP;

END;


Excluded Mondays in August
2015-08-03 1st Monday
2015-08-10 2nd Monday
2015-08-17 3rd Monday
2015-08-24 4th Monday
2016-08-01 1st Monday
2016-08-08 2nd Monday
2016-08-15 3rd Monday
2016-08-22 4th Monday
2017-08-07 1st Monday
2017-08-14 2nd Monday

Excluded Mondays in July
2015-07-13 2nd Monday
2015-07-20 3rd Monday
2015-07-27 4th Monday
2016-07-11 2nd Monday
2016-07-18 3rd Monday
2016-07-25 4th Monday
2017-07-10 2nd Monday
2017-07-17 3rd Monday
2017-07-24 4th Monday
2017-07-31 5th Monday

Executions Job 1
2015-06-15 3rd Monday
2015-06-22 4th Monday
2015-06-29 5th Monday
2015-07-06 1st Monday
2015-08-31 5th Monday
2015-09-07 1st Monday
2015-09-14 2nd Monday
2016-06-20 3rd Monday
2016-06-27 4th Monday
2016-07-04 1st Monday
2016-08-29 5th Monday
2016-09-05 1st Monday
2016-09-12 2nd Monday
2016-09-19 3rd Monday
2017-06-19 3rd Monday
2017-06-26 4th Monday
2017-07-03 1st Monday
2017-08-28 4th Monday
2017-09-04 1st Monday
2017-09-11 2nd Monday
2017-09-18 3rd Monday
2018-06-18 3rd Monday
2018-06-25 4th Monday
2018-07-02 1st Monday

Executions Job 2
2015-06-21 3rd Sunday
2015-06-28 4th Sunday
2015-07-05 1st Sunday
2015-07-12 2nd Sunday
2015-09-06 1st Sunday
2015-09-13 2nd Sunday
2015-09-20 3rd Sunday
2016-06-26 4th Sunday
2016-07-03 1st Sunday
2016-07-10 2nd Sunday
2016-09-04 1st Sunday
2016-09-11 2nd Sunday
2016-09-18 3rd Sunday
2016-09-25 4th Sunday
2017-06-25 4th Sunday
2017-07-02 1st Sunday
2017-07-09 2nd Sunday
2017-09-03 1st Sunday
2017-09-10 2nd Sunday
2017-09-17 3rd Sunday
2017-09-24 4th Sunday
2018-06-24 4th Sunday
2018-07-01 1st Sunday
2018-07-08 2nd Sunday

How to set start_date and end_date on a dbms job (oracle)

3 Answers3