Other Oracle database topics - dbms_job resubmitting itself

Author

dbms_job resubmitting itself

daniel.ostertag@visaer.com

2005-09-26, 11:23 am

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

stephen O'D

2005-09-26, 8:23 pm

daniel.ostertag@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.

Sponsored Links