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