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 ?

Answer 1

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;