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
mail96
Messages: 1
Registered: June 2008
Junior Member
Hi,

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 =>
'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
next_day(trunc(sysdate),''WEDNESDAY'')+5/24)
END');


--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
next_day(trunc(sysdate),'WEDNESDAY')+5/24)
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: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See http://www.orafaq.com/forum/m/313430/102589/?srch=NextJobDate#msg_313430

Regards
Michel
Previous Topic: schema level
Next Topic: sqlplus hangs when running an SQL script
Goto Forum:
  


Current Time: Mon Dec 05 12:40:28 CST 2016

Total time taken to generate the page: 0.11645 seconds