Re: dbms_job resubmitting itself

daniel.ostertag_at_visaer.com wrote:
> I have created an Oracle Job using the dbms_job.submit package. It is
> scheduled to run every night at midnight (trunc(sysdate+1) I am told is
> the midnight syntax), it is indeed running successfully every night at
> midnight, but it is also creating a new job, with a new job# each time
> it runs, leaving 2 identical jobs in the database each with identical
> specs.
>
> Here is my job submit command:
> interval := 'SYSDATE+1'; -- once a day
> dbms_job.submit(
> jobnum,'V_PURGE_LIC_DATA;',TRUNC(SYSDATE+1),interval);
>
> What am I doing wrong? I've used this syntax before without problems,
> but never used the every night at midnight command.
>
> Thanks,
> Dan

Are you sure you V_PURGE_LIC_DATA code is not submitting a job itself? After dbms_job completes it does not create a new job entry, it just leaves the same entry in user_jobs and updates the next time etc.

I think you interval parameter should be 'trunc(sysdate) + 1' like your 'when' parameter too, otherwise you could get the 'sliding job' problem where it drifts after midnight a little more each day. Received on Mon Sep 26 2005 - 14:59:46 CDT