Home » SQL & PL/SQL » SQL & PL/SQL » Schedule DBMS job every day in week except Friday
Schedule DBMS job every day in week except Friday [message #289128] Wed, 19 December 2007 18:31 Go to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
Hi All ,

I have a existing DBMS job .

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'begin
-- Call the procedure
sp_execute_procedure ;
end;'
,next_date => to_date('19/12/2007 22:15:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE) + 185/96'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;

commit;

Got the script from Toad .
===============================================
This procedure runs every day at 10: 15 PM .Can Anyone explain me how this interval and next date works .185/96 here implies that this is 10: 15 of next day from sysdate. I want this job to be modified to run every day in the week except FRIDAY. Please help .

[Updated on: Wed, 19 December 2007 18:45]

Report message to a moderator

Re: Schedule DBMS job every day in week except Friday [message #289132 is a reply to message #289128] Wed, 19 December 2007 18:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
In My Opinion, you have a couple of options.
1) Within the procedure check for Friday & exit when it is Friday.
2) Create 6 WEEKLY entries for this task (Sun, Mon, Tue, Wed,Thurs, Sat)

[Updated on: Wed, 19 December 2007 18:54] by Moderator

Report message to a moderator

Re: Schedule DBMS job every day in week except Friday [message #289138 is a reply to message #289132] Wed, 19 December 2007 19:06 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
I am trying to do that ..
least(
next_day(SYSDATE,''MONDAY''),
next_day(SYSDATE,''TUESDAY''),
next_day(SYSDATE,''WEDNESDAY''),
next_day(SYSDATE,''THURSDAY''),
next_day(SYSDATE,''SATURDAY'') ,
next_day(SYSDATE,''SUNDAY'')
)

But Some how its not working..

Report message to a moderator

Re: Schedule DBMS job every day in week except Friday [message #289140 is a reply to message #289128] Wed, 19 December 2007 19:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SIX separate DBMS_JOB entries each of which runs once a week on a specific day of the week.

Report message to a moderator

Re: Schedule DBMS job every day in week except Friday [message #289154 is a reply to message #289132] Wed, 19 December 2007 21:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
anacedent wrote on Wed, 19 December 2007 16:54

In My Opinion, you have a couple of options.
1) Within the procedure check for Friday & exit when it is Friday.
2) Create 6 WEEKLY entries for this task (Sun, Mon, Tue, Wed,Thurs, Sat)


3) ,interval => 'trunc (sysdate) + decode (to_char (sysdate, 'dy'), 'thu', 2, 1) + 185/96'

Report message to a moderator

Re: Schedule DBMS job every day in week except Friday [message #289185 is a reply to message #289154] Thu, 20 December 2007 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

interval => 'trunc (sysdate) + decode (to_char (sysdate, 'dy'), 'thu', 2, 1) + 185/96'

I think you meant
interval => 'trunc (sysdate) + decode (to_char (sysdate, 'dy'), 'thu', 1, 0) + 185/96' 

what => 'begin
-- Call the procedure
if to_char(sysdate,'Dy') != 'Fri' then
sp_execute_procedure ;
end if;
end;'


OP, next time: read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Also post your Oracle version (at least 3 decimals).

Regards
Michel

Report message to a moderator

Re: Schedule DBMS job every day in week except Friday [message #289308 is a reply to message #289185] Thu, 20 December 2007 11:30 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Wed, 19 December 2007 22:46

Quote:

interval => 'trunc (sysdate) + decode (to_char (sysdate, 'dy'), 'thu', 2, 1) + 185/96'

I think you meant
interval => 'trunc (sysdate) + decode (to_char (sysdate, 'dy'), 'thu', 1, 0) + 185/96'




Yes, thanks for the correction.

Report message to a moderator

Previous Topic: Select Records Whether or Not They Exist In Another Table
Next Topic: Asking about PK & FK Relations
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Aug 06 11:56:21 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.