Home » SQL & PL/SQL » SQL & PL/SQL » avoid duplicate runs on dbms jobs
avoid duplicate runs on dbms jobs [message #329525] Wed, 25 June 2008 12:28 Go to next message
Messages: 1
Registered: June 2008
Junior Member

I have a requirement to run a job at the beginning of the month and every Wednesday of each week.

I'm trying to get around running the job twice when they fall on the same day.

I tried the following in the interval parameter, but dbms_job does not like it.

interval =>
WHEN to_char(to_date(to_char(trunc(sysdate ,''MONTH'') + 1)), ''Day'') = ''Wednesday'' THEN
next_day(trunc(sysdate),''WEDNESDAY'')+5/24 )
WHEN to_char(to_date(to_char( trunc(sysdate ,''MONTH'') + 1)),''Day'') <> ''Wednesday'' THEN
next_day(trunc(sysdate,''MONTH'') + 1)+5/24
WHEN to_char(to_date(to_char( trunc(sysdate,''MONTH'') + 1)),''Day'') <> ''Wednesday'' THEN

--error output
ORA-23319: parameter value "CASE WHEN to_char(to_date(to_char(trunc(sysdate ,'MONTH') + 1)), 'Day') = 'Wednesday' THEN
next_day(trunc(sysdate),'WEDNESDAY')+5/24 )
WHEN to_char(to_date(to_char( trunc(sysdate ,'MONTH') + 1)),'Day') <> 'Wednesday' THEN
next_day(trunc(sysdate,'MONTH') + 1)+5/24
WHEN to_char(to_date(to_char( trunc(sysdate,'MONTH') + 1)),'Day') <> 'Wednesday' THEN
END" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 230
ORA-06512: at "SYS.DBMS_JOB", line 186
ORA-06512: at line 2

Can help me get around this?

Thank you.
Re: avoid duplicate runs on dbms jobs [message #329527 is a reply to message #329525] Wed, 25 June 2008 12:50 Go to previous message
Michel Cadot
Messages: 65144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See http://www.orafaq.com/forum/m/313430/102589/?srch=NextJobDate#msg_313430

Previous Topic: sqlplus hangs when running an SQL script
Next Topic: Use of Oracle Decode function
Goto Forum:

Current Time: Sun Aug 20 19:59:07 CDT 2017

Total time taken to generate the page: 0.01532 seconds