Oracle APEX Automation Schedule

Question

Is it possible to exceute oracle apex automation on a specific day of the month(example every 28th of the month),,,once a month,,monthly? so far the settings only offer - weekly, daily,etc,

Is it possible to tweak through the Schedule Expression? (Example: FREQ=DAILY;INTERVAL=1;BYHOUR=23;BYMINUTE=0)

Thanks in advance.

Answer 1

You want it to fire every month, so FREQ=MONTHLY;INTERVAL=1. Then it should be on the 28th day of the month, so BYMONTHDAY=28. Hours and minutes don't really matter so just put BYHOUR=8;BYMINUTE=0 or whatever you prefer.

There is a very simple way to test this. APEX automation use the oracle scheduling calendar syntax, which can be evaluated using dbms_scheduler.evaluate_calendar_string

DECLARE
 start_date        TIMESTAMP;
 return_date_after TIMESTAMP;
 next_run_date     TIMESTAMP;
 l_interval        VARCHAR2(500) := 'FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=28;BYHOUR=8;BYMINUTE=0';
BEGIN
  start_date := current_timestamp;
  return_date_after := start_date;
  FOR i IN 1..5
  LOOP
    dbms_scheduler.evaluate_calendar_string
    (
      l_interval, 
      start_date,
      return_date_after, 
      next_run_date
    );
    
    dbms_output.put_line('next_run_date: ' || TO_CHAR(next_run_date,'fmDay, DD-MON-YYYY HH24:MI SS'));
    return_date_after := next_run_date;
  END LOOP;
END;
/



next_run_date: Sunday, 28-NOV-2021 8:0 59
next_run_date: Tuesday, 28-DEC-2021 8:0 59
next_run_date: Friday, 28-JAN-2022 8:0 59
next_run_date: Monday, 28-FEB-2022 8:0 59
next_run_date: Monday, 28-MAR-2022 8:0 59