DBMS_JOB Setting next date to run.

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Fri, 07 Dec 2001 18:29:02 GMT
Message-ID: <3c110692.1568795578@news.alt.net>

Oracle 8.1.6

I have a table that acts as a queue.

Queue

---

Id
Queue_Date

When the Queue_Date is reached, I need a procedure to be run.

To run the procedure I chose DBMS_JOB. An AFTER TRIGGER (ON INSERT OR UPDATE OR DELETE) resets NEXT_DATE to be MIN(Queue_Date). So far, this all works well.

After that, the procedure either DELETEs or UPDATEs Queue_Date. Here is the problem, or what I believe it to be. The trigger runs before DBMS_JOB applies INTERVAL. So no matter what the trigger does to Queue_Date, when called by the job, INTERVAL overwrites whatever the trigger set. (INTERVAL needs to be set, so the job doesn't disappear after its first run.)

I was thnking of resetting INTERVAL to evaluate to MIN(Queue_Date). However, that does not help when the table is UPDATEd by putting more entries into the Queue. That would have to reset NEXT_DATE not INTERVAL. Which means that I would have to know if DBMS_JOB is the one that called the procedure that invoked the UPDATE before I could decide which to reset (NEXT_DATE or INTERVAL).

I would appreciate any input given on this subject.

Brian Received on Fri Dec 07 2001 - 12:29:02 CST