Re: complex dbms jobs code

James A. Williams wrote:
>
> 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

An easy way is to pass next_date to your proc:

procedure the_job(p_dte in out date) is
begin

• some stuff p_dte := ... end;

and then submit your job with

'the_job(next_date);'

then you have full control

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."