Re: complex dbms jobs code

On Thu, 02 May 2002 10:21:18 GMT, willjamu_at_mindspring.com (James A. Williams) wrote:

The below may work I am testing it at the moment. I have a cron job that does it at the moment since it is new code on a test machine. I would like to use dbms_jobs since its in the database.

>cat jobtest3.sql

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

REM -- will run a job every weekday every 30 minutes VARIABLE jobno number;
BEGIN
sys.dbms_job.submit(:jobno,
'test.jaw_insert(420,sysdate);',
TRUNC(SYSDATE+1),
'decode(trim(to_char(to_date(sysdate)+30/1440,''DAY'')), ''SATURDAY'',
(to_date(sysdate)+2+30/1440),
to_date(sysdate)+30/1440)');
END;
>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 - 14:09:51 CDT