Create a function that returns the next desired Queue_Date as a date
value. Set the function call as the NEXT_DATE using dbms_job.next_date.
HTH
tED
"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in
message news:3c110692.1568795578_at_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 - 15:44:14 CST
