AW: DBMS_JOB -- urgent !!!

From: Haunschmidt Andreas VASL/FAS <Andreas.Haunschmidt_at_voest.co.at>
Date: Fri, 02 Mar 2001 13:40:11 -0800
Message-ID: <F001.002C2CBB.20010302131813@fatcity.com>

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:p_rodri99_at_yahoo.es]
> 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 !!!
>
>
>
>
>
>
> --- Scott.Shafer_at_dcpds.cpms.osd.mil 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:p_rodri99_at_yahoo.es]
> > > 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: p_rodri99_at_yahoo.es
> > >
> > > 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: ListGuru_at_fatcity.com (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: p_rodri99_at_yahoo.es
>
> 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: ListGuru_at_fatcity.com (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: Andreas.Haunschmidt_at_voest.co.at

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: ListGuru_at_fatcity.com (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).

Received on Fri Mar 02 2001 - 15:40:11 CST