Re: About DBMS_JOB Package question.

From: Adrian Hands <AHands_at_sprynet.com>
Date: Tue, 19 May 1998 21:56:16 -0400
Message-ID: <35623840.B54E869E@sprynet.com>

lee wrote:

> Interval:=15;
> DBMS_JOB.SUBMIT(JobNo,
> 'MyProcedure(arg,...)',
> StartTime,
> 'DECODE(ceil(EndTime-SysDate),0,NULL,SysDate+Interval/1440)',false);

The 'DECODE...' parameter is a varchar2 string that is executed by the job to schedule the subsequent execution. Therefore it is running in another session and can't see your "Interval" variable. In fact, your "Interval" variable probably has gone out of scope by then anyway.
Try something like:

Interval:=15;
DBMS_JOB.SUBMIT(JobNo,
   'MyProcedure(arg,...)',
    StartTime,
   'DECODE(ceil(TO_DATE(''' || EndTime || ''')-SysDate),0,NULL,SysDate+' || Interval || '/1440)',false); Received on Tue May 19 1998 - 20:56:16 CDT