Menu
I have a procedure named MY_PROCEDURE_X in a packaged MY_PACKAGE_X. My requirement is that the procedure need to be executed on 1st and 16th of every month. If it is running on 1st of the Month, then the time of execution should be 10:00 AM, If it is running on 16th of the Month, then the time of execution should be 05:00 PM.
Can I make a single job to do this both? Below is my half done script:
BEGIN
dbms_scheduler.create_job (
job_name => 'PROCESS_MY_JOB_X',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'MY_PACKAGE_X.MY_PROCEDURE_X',
START_DATE => TO_DATE('01-11-2014 10:00','DD-MM-YYYY HH24:MI'),
repeat_interval => 'FREQ=DAILY; INTERVAL=14',
ENABLED => TRUE,
comments => 'RUN JOB ON 1ST AND 16TH OF EVERY MONTH');
END;
/
Thanks in advance ;)
679
The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures that are callable from any PL/SQL program. See Also: Oracle Database Administrator's Guide for more information regarding how to use DBMS_SCHEDULER.
dbms_scheduler is more robust and fully-featured than dbms_job and includes the following features that dbms_job does not have : logging of job runs (job history) simple but powerful scheduling syntax (similar to but more powerful than cron syntax) running of jobs outside of the database on the operating system.
Run your job SQL> begin 2 dbms_scheduler. run_job('TEST_abc',TRUE); 3* end; SQL> / PL/SQL procedure successfully completed. SQL> select job_name,enabled,run_count from user_scheduler_jobs; JOB_NAME ENABL RUN_COUNT ------------------------------ ----- ---------- TEST_ABC FALSE 0 Copying Jobs SQL> begin 2 dbms_scheduler.
auto_drop. This flag, if TRUE, causes a job to be automatically dropped after it has completed or has been automatically disabled. A job is considered completed if: Its end date (or the end date of the job schedule) has passed. It has run max_runs number of times.
EDIT: My previous answer did not actually work. BYHOUR could not be used in the named schedule for some reason - it gave an error.
Instead I discovered a way to do it in a single repeat_interval expression:
'FREQ=MONTHLY;BYMONTHDAY=1,16;BYHOUR=10,17;BYSETPOS=1,4'
The trick here is that BYMONTHDAY=1,16 and BYHOUR=10,17 actually creates a set of four date/times:
The 1st at 10, the 1st at 17, the 16th at 10, the 16th at 17
Then BYSETPOS=1,4 picks the 1st and 4th date/times out of the set of four, and that is the two date/times we want.
One can always test a repeat_interval expression using dbms_scheduler.evaluate_calendar_string like for example:
declare
startdate date;
dateafter date;
nextdate date;
begin
startdate := TO_DATE('01-11-2014 10:00','DD-MM-YYYY HH24:MI');
dateafter := startdate;
for i in 1..24 loop
dbms_scheduler.evaluate_calendar_string(
'FREQ=MONTHLY;BYMONTHDAY=1,16;BYHOUR=10,17;BYSETPOS=1,4'
, startdate
, dateafter
, nextdate
);
dbms_output.put_line(to_char(nextdate,'YYYY-MM-DD HH24:MI'));
dateafter := nextdate;
end loop;
end;
/
That block outputs this result:
2014-11-16 17:00
2014-12-01 10:00
2014-12-16 17:00
2015-01-01 10:00
2015-01-16 17:00
2015-02-01 10:00
2015-02-16 17:00
2015-03-01 10:00
2015-03-16 17:00
2015-04-01 10:00
2015-04-16 17:00
2015-05-01 10:00
2015-05-16 17:00
2015-06-01 10:00
2015-06-16 17:00
2015-07-01 10:00
2015-07-16 17:00
2015-08-01 10:00
2015-08-16 17:00
2015-09-01 10:00
2015-09-16 17:00
2015-10-01 10:00
2015-10-16 17:00
2015-11-01 10:00
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
