-
Title
How to schedule jobs to execute on specific days? -
Description
How do I schedule a job to run on specific days such as only on weekdays (Monday thru Friday)?
-
Resolution
This should be possible in Oracle. Please check Oracle documentation for specifying other days.
Monday through Friday Example
The following example shows how to schedule a job to be executed Monday through Friday:
DBMS_JOB.SUBMIT (:v_jobnum,'my_procedure;', sysdate,
'TRUNC(LEAST(NEXT_DAY(SYSDATE,''MONDAY''),
NEXT_DAY(SYSDATE,''TUESDAY''),
NEXT_DAY(SYSDATE,''WEDNESDAY''),
NEXT_DAY(SYSDATE,''THURSDAY''),
NEXT_DAY(SYSDATE,''FRIDAY'') ))');
The following example shows how to schedule a job to be executes Monday through Friday at 6:00 pm:
DBMS_JOB.SUBMIT (:v_jobnum,'my_procedure;', sysdate,
'TRUNC(LEAST(NEXT_DAY(SYSDATE,''MONDAY''),
NEXT_DAY(SYSDATE,''TUESDAY''),
NEXT_DAY(SYSDATE,''WEDNESDAY''),
NEXT_DAY(SYSDATE,''THURSDAY''),
NEXT_DAY(SYSDATE,''FRIDAY'') )) + 18/24');
Notes:
** Two single quotes are used around the day of the week, not double quotes.
** Ensure that the 4th parameter is all on one line. Allow the line to wrap if necessary.
IN TOAD:
The user can change whatâ??s in that drop down of the Schema Browser | Jobs tab | Create Job - Subsequent Executions by editing the jobsdates.txt file in the user files directory.
Just enter it like this:
(extra spaces taken out, doubled up single quotes changed to single single quotes)
TRUNC(LEAST(NEXT_DAY(SYSDATE,'MONDAY'), NEXT_DAY(SYSDATE,'TUESDAY'), NEXT_DAY(SYSDATE,'WEDNESDAY'), NEXT_DAY(SYSDATE,'THURSDAY'), NEXT_DAY(SYSDATE,'FRIDAY') )) + 18/24