Re: complex dbms jobs code

From: Mark <markg_at_mymail.co.uk>
Date: 2 May 2002 07:43:14 -0700
Message-ID: <ddb31653.0205020643.3ebdb067@posting.google.com>

Why not wrap ypur procedure in another and call it every minute? Check the date, if the minute is 30 and the day is MON, TUE, WED or THU, execute your main procedure

e.g

Setup DBMS_JOB to run DO_JOB every minute continuosly. If date criteria matches, call your proc.

Procedure Do_Job

Begin

   IF (TO_CHAR(SYSDATE, 'MI') = '30' OR TO_CHAR(SYSDATE, 'MI') = '00')

      AND
      (TO_CHAR(SYSDATE, 'DAY') = 'MONDAY' OR
       TO_CHAR(SYSDATE, 'DAY') = 'TUESDAY' OR
       TO_CHAR(SYSDATE, 'DAY') = 'WEDNESDAY' OR
       TO_CHAR(SYSDATE, 'DAY') = 'THURSDAY') THEN

     aglcust.lookup_update.cas_schedule;

   END IF; End Do_Job;

HTH, M   

willjamu_at_mindspring.com (James A. Williams) wrote in message news:<3cd11271.1077108_at_news.mindspring.com>...
> Oracle 8.1.7.3 on Solaris. I have the below. I need to run a job
> Monday through Thursday . It needs to run every 30 minutes at 00
> and 30 minutes after each hour. The below only schedule for 12:30
> each day. Any help on improving the interval to get this to work
> would be cool!
>
>
> VARIABLE jobno number;
> BEGIN
> sys.dbms_job.submit(:jobno,
> 'aglcust.lookup_update.cas_schedule;',
> sysdate,
> 'TRUNC(LEAST(NEXT_DAY(SYSDATE,''MONDAY''),
> NEXT_DAY(SYSDATE,''TUESDAY''),
> NEXT_DAY(SYSDATE,''WEDNESDAY''),
> NEXT_DAY(SYSDATE,''THURSDAY'') ) ) + 30/1440');
> END;
>
>
>
>
> JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE
> LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
> NEXT_SEC TOTAL_TIME BROKEN INTERVAL FAILURES
> WHAT NLS_ENV MISC_ENV INSTANCE
> 125 AGLCUST AGLCUST AGLCUST 5/2/2002 12:30:29 AM 00:30:29
> 5/6/2002 12:30:00 AM 00:30:00 653 N
> TRUNC(LEAST(NEXT_DAY(SYSDATE,'MONDAY'),
> NEXT_DAY(SYSDATE,'TUESDAY'),
> NEXT_DAY(SYSDATE,'WEDNESDAY'),
> NEXT_DAY(SYSDATE,'THURSDAY') ) ) + 30/1440 0
> aglcust.lookup_update.cas_schedule; NLS_LANGUAGE='AMERICAN'
> NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA'
> NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='YYYYMMDD'
> NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' 0102000200000000
> 0
Received on Thu May 02 2002 - 09:43:14 CDT