Home how to run a JOB in the oracle only once a month, on the first day of the month

Questions

how to run a JOB in the oracle only once a month, on the first day of the month

February 2, 2022

I wrote the process, and I would like it to be performed only once a month (on the first day of the month). Here is my code, but it is necessary to adjust the job start interval. And I can not understand how to enter the first day of the month. Also I want to do a similar job but to run it only on the 25th of each month. I will be grateful for your help!

begin
  dbms_scheduler.create_job (
     job_name           =>  'My_Job',
     job_type           =>  'STORED_PROCEDURE',
     job_action         =>  'Ins_Test2',
     start_date         =>  systimestamp, 
     repeat_interval    =>  'freq=minutely; interval = 20; byday=MON,TUE,WED,THU,FRI;',
     enabled            =>  true,
     comments           => 'My new job'
  );
end;

>Solution :

Set the interval to

repeat_interval    =>  'FREQ=MONTHLY;BYMONTHDAY=25'

To evaluate the calendar expression, use DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING. Example:

DECLARE
start_date        TIMESTAMP WITH time zone;
return_date_after TIMESTAMP WITH time zone;
next_run_date     TIMESTAMP WITH time zone;
BEGIN
-- start date of schedule
start_date :=
  to_timestamp_tz('CST 12:00 05-MAY-2012','TZR HH24:MI DD-MON-RRRR');
return_date_after := start_date;
FOR i IN 1..5 LOOP
  DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(  
    'FREQ=MONTHLY;BYMONTHDAY=25',
    start_date, return_date_after, next_run_date);
DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date at time zone 'PST');
return_date_after := next_run_date;
END LOOP;
END;
/

next_run_date: 25-MAY-12 10.00.00.000000000 AM
next_run_date: 25-JUN-12 10.00.00.000000000 AM
next_run_date: 25-JUL-12 10.00.00.000000000 AM
next_run_date: 25-AUG-12 10.00.00.000000000 AM
next_run_date: 25-SEP-12 10.00.00.000000000 AM