Solved

How to process a scheduled database task n times per day

  • 1 February 2024
  • 9 replies
  • 161 views
Link
Rank
Userlevel 7
Badge +22
  • Link
  • Superhero (Customer)
  • 945 replies

Dear community,

i wonder if I can run a scheduled database task 3 times a day and during the week.

For example:

From mondays to fridays

and every day at 6:00, 12:00 and 5:00 o’clock

icon

Best answer by dsj 1 February 2024, 17:07

View original
Kind regards

9 replies

dsj
Rank
Userlevel 7
Badge +20

Hi @Link 

 

You can choose a custom schedule in the Schedule database task and give the DBMS Scheduler expression for your requirement.

I think this expression would work for you :)

FREQ=DAILY;BYHOUR=6,12,17;BYMINUTE=0;BYDAY=MON,TUE,WED,THU,FRI

 

Check this topic for some good discussion on custom expressions in scheduled tasks

Scheduled Database Task between certain times | IFS Community

 

Hope it helps!

Damith

DSJ - https://dsj23.me
Link
Rank
Userlevel 7
Badge +22

Hi @dsj 

thank you a lot.

What to do if I have times like 6:30, 12:00 and 17:15?

Do I need to create 3 database tasks?

Kind regards
NickPorter
Rank
Userlevel 6
Badge +18

For this kind of scheduling where the timing is not equally spread out in the day (e.g. at specific times and not every x hours), I would schedule them as 3 different tasks.  This is simple to do and will give you the control you need, as well as making it easiest to adjust just one of the jobs in the future.

Nick

Link
Rank
Userlevel 7
Badge +22

For this kind of scheduling where the timing is not equally spread out in the day (e.g. at specific times and not every x hours), I would schedule them as 3 different tasks.  This is simple to do and will give you the control you need, as well as making it easiest to adjust just one of the jobs in the future.

Nick

Hi @NickPorter,

yes, I agree. But you will have too many jobs to manage. 

Kind regards
NickPorter
Rank
Userlevel 6
Badge +18

For this kind of scheduling where the timing is not equally spread out in the day (e.g. at specific times and not every x hours), I would schedule them as 3 different tasks.  This is simple to do and will give you the control you need, as well as making it easiest to adjust just one of the jobs in the future.

Nick

Hi @NickPorter,

yes, I agree. But you will have too many jobs to manage. 

Well, that really depends on how many of these you will have that need to run at irregular times.  In your example your asked about there would be only 3 jobs (rather than 1) so that is still the approach I would recommend. 

In reality I would be really surprised if you truly have such a large number of jobs that MUST run at irregular times during a day that requires this kind of split into different jobs.  If so, I would really try to align the execution times to specific minutes and implement similar to what @dsj suggested.  But again, I really can’t foresee why you would get to ‘too many jobs to manage’ using this suggested approach if you used it when you needed to, and generally you wouldn't likely need to manage them often after creation anyway.

HTH,

Nick

Link
Rank
Userlevel 7
Badge +22

Hi @NickPorter 

we have about 7 companies worldwide and if everybody needs 3 iterations at different times, we will have 21 jobs. 😀

Then I will try to standardise the scheduled times.

Thank you a lot. 👍🏼

 

 

Kind regards
dsj
Rank
Userlevel 7
Badge +20

Hi @NickPorter 

we have about 7 companies worldwide and if everybody needs 3 iterations at different times, we will have 21 jobs. 😀

Then I will try to standardise the scheduled times.

Thank you a lot. 👍🏼

 

 

 

I think you don’t need to schedule 21 jobs ;)

This formula should work for your need to schedule it on 6:30, 12:00 and 17:15 every weekday

FREQ=DAILY; BYTIME=063000,120000,171500; BYDAY=MON,TUE,WED,THU,FRI

 

For more info, refer https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SCHEDULER.html#GUID-A24DEB5D-2EAF-4C0B-8715-30DC947B3F87

Hope it helps!

Damith

DSJ - https://dsj23.me
dsj
Rank
Userlevel 7
Badge +20

If you need to validate the scheduler formula, you can use below simple script which will output next 10 run date/times

DECLARE
  start_date        TIMESTAMP;
  return_date_after TIMESTAMP;
  next_run          TIMESTAMP;
BEGIN
  start_date        := SYSTIMESTAMP;
  return_date_after := start_date;
  FOR i IN 1 .. 10 LOOP
    DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY; BYTIME=063000,120000,171500; BYDAY=MON,TUE,WED,THU,FRI',
                                            start_date,
                                            return_date_after,
                                            next_run);
    DBMS_OUTPUT.PUT_LINE('Next Run: ' || next_run);
    return_date_after := next_run;
  END LOOP;
END;

 

DSJ - https://dsj23.me
Link
Rank
Userlevel 7
Badge +22

Hi @NickPorter 

we have about 7 companies worldwide and if everybody needs 3 iterations at different times, we will have 21 jobs. 😀

Then I will try to standardise the scheduled times.

Thank you a lot. 👍🏼

 

I think you don’t need to schedule 21 jobs ;)

This formula should work for your need to schedule it on 6:30, 12:00 and 17:15 every weekday

FREQ=DAILY; BYTIME=063000,120000,171500; BYDAY=MON,TUE,WED,THU,FRI

 

For more info, refer https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SCHEDULER.html#GUID-A24DEB5D-2EAF-4C0B-8715-30DC947B3F87

Hope it helps!

Damith

 

 

Hi @dsj 

a very good idea. It is cool. 😄

Thank you again. 

 

Kind regards

Reply


Terms & ConditionsCookie settings