Re: Using dbms_job package

From: David Martin <djmartin_at_netspace.net.au>
Date: 1997/10/16
Message-ID: <34457f2a.11396627_at_news.netspace.net.au>#1/1

Mark Pether <markap_at_bom.gov.au> wrote:

<snip>
<add previous posting
>>>declare
>>> job integer;
>>>begin
>>> dbms_job.submit(job,'fred;',sysdate+10/60/24,'sysdate+10/60/24'); --
>>>every 10 minutes
>>> dbms_output.put_line('job in queue: '||job);
>>>end;
>>
>> Add commit;
>>

 <snip>
>declare
> job# integer;
>begin
> dbms_output.put_line('submit time: '||to_char(sysdate,'hh24:mi:ss'));
> dbms_job.submit(job#,'fred;',sysdate,'sysdate+2/60/24');
> commit; -- <<<<<<<<< new improved with commit!!!
> dbms_output.put_line(job#);
> dbms_output.put_line('finish time: '||to_char(sysdate,'hh24:mi:ss'));
>end;

<snip>

Mark,

I have just had a similar problem to your own when I tried to run the following:

var x number;
execute dbms_job.submit(:x,'fred;',sysdate,'sysdate + 3/(24*60)'); print x;
commit;

It loaded, but would not run.

What I believe is wrong, is that the sysdate has already PASSED as far as the internal processing is concerned and a flag of some kind has been set. Therefore, it is marked as NOT OKAY for future processing. (Check out the 'FAILURES' field of ALL_JOBS, when you load a new job and again after it has been run. It is null on load ('.SUBMIT') but 0 when running okay.) I did a 'dbms_job.run' to "kick-start" it, and from then on it was okay. I believe that if you had 'commit'ed your _first_ example within 2 minutes of loading it, it would have worked. However, as your second example had 'sysdate' only, the 'commit' was not enough, it needed to be kick-started past the current time.

I'll ring you to follow it up.

David

---

 _--_|\     PO Box 139, Belair, South Australia, 5052
/      \    Tel:+61 3 5173-2273 Fax:+61 3 5173-2991
\_.-*._/    email: djmartin at netspace.net.au (Work things)
    ^ v          : davidj at adam.com.au (Adelaide stuff)
    |       "Don't fight the decision, fight its basis!"

    '----<---<---<---<---<---<---<---<---<---<---<---<---<---<- Received on Thu Oct 16 1997 - 00:00:00 CEST