Hi!
----------------------------------------------------------------
Create a function:
----------------------------------------------------------------
create or replace
function all_days_except_sat_sun (iDate IN date)
return date
is
oDate date;
begin
select decode (
to_number(to_char(iDate,'D')),
1, iDate+1, -- if Sunday then delay to Monday
7, iDate+2, -- if Saturday then delay to Monday ***
iDate) -- default new date
into oDate from dual;
return oDate;
end;
/
----------------------------------------------------------------
-- Test for function all_days_except_sat_sun
----------------------------------------------------------------
set serveroutput on
declare
a date := trunc(sysdate); -- start point
n date; -- new calculated date
h number := 22; -- start hour
x date; -- date to execute
d1 varchar2(10); -- for the day name
d2 varchar2(10); -- for the day name
/*
SUNDAY 1
MONDAY 2
TUESDAY 3
WEDNESDAY4
THURSDAY 5
FRIDAY 6
SATURDAY 7
*/
begin
-- 14 day check
for i in 0..13 loop
n := a + i + h/24; -- calculate new date
x:=all_days_except_sat_sun(n); -- correct execution date
select to_char(n,'DAY') into d1 from dual;
select to_char(x,'DAY') into d2 from dual;
dbms_output.put_line(n || ' DAY ' || d1 || ' -> ' || x || ' DAY:' || d2);
end loop;
end;
/
----------------------------------------------------------------
So you could specify for the job's interval
'all_days_except_sat_sun(trunc(sysdate)+22/24))'
For jobs to run except Sunday just create a 2nd function
and remove the line marked with ***
Hope this helped
Andreas.
> ----------
> Von: Pablo ksksksk[SMTP:[EMAIL PROTECTED]]
> Gesendet: Freitag, 2. März 2001 19:45
> An: Multiple recipients of list ORACLE-L
> Betreff: RE: DBMS_JOB -- urgent !!!
>
>
> Thanks for answering
>
> That's not what I'm looking for, I don't want to do it
> with UNIX crontab.
>
> I need to do it by using DBMS_JOB package !!!
>
>
>
>
>
>
> --- [EMAIL PROTECTED] escribió: > use
> 1-6 for every day except Sunday and
> > 1-5 for every day except Saturday and Sunday.
> > Days in cron are labelled 0-6, starting with Sunday,
> > respectively.
> >
> > i.e.,
> >
> > 00 17 * * 1-6 /opt/oracle/admin/scripts/
> > 00 17 * * 1-5 /opt/oracle/admin/scripts/
> >
> > --Scott Shafer
> > San Antonio, TX
> >
> > > -----Original Message-----
> > > From: Pablo ksksksk [SMTP:[EMAIL PROTECTED]]
> > > Sent: Friday, March 02, 2001 8:51 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: DBMS_JOB -- urgent !!!
> > >
> > > Hi list,
> > > I need to cron a job every day at 10pm except
> > > sundays, how do I acomplish this ? I need the
> > INTERVAL
> > > field.
> > >
> > > I also need to cron another job every day
> > except
> > > sundays an saturdays too, can you please help me
> > with
> > > this?
> > >
> > > thanks in advance.
> > >
> > >
> >
> _______________________________________________________________
> > > Do You Yahoo!?
> > > Envía mensajes instantáneos y recibe alertas de
> > correo con
> > > Yahoo! Messenger - http://messenger.yahoo.es
> > > --
> > > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > > --
> > > Author: =?iso-8859-1?q?Pablo=20ksksksk?=
> > > INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services -- (858) 538-5051
> > FAX: (858) 538-5051
> > > San Diego, California -- Public Internet
> > access / Mailing Lists
> > >
> >
> --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > > (or the name of mailing list you want to be
> > removed from). You may
> > > also send the HELP command for other information
> > (like subscribing).
>
>
> _______________________________________________________________
> Do You Yahoo!?
> Envía mensajes instantáneos y recibe alertas de correo con
> Yahoo! Messenger - http://messenger.yahoo.es
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Pablo=20ksksksk?=
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Haunschmidt Andreas VASL/FAS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
