This discussion is archived
2 Replies Latest reply: Aug 9, 2012 6:20 AM by xerces8 RSS

Prevent multiple instances of same job in a transaction?

xerces8 Newbie
Currently Being Moderated
Hi!

We are starting an asynchronous job from a table trigger (AFTER INSERT) like this: dbms_job.submit (jobNr,'package.procedure;');

The procedure does some background processing, involving remote database access (that is why we don't want to do it synchronously, to keep foreground tasks fast).

The problem is, the trigger is activated multiple times during the same transaction, which we do not want (and overloads the DB in some cases with thousands of jobs).


It there a way to check if the job is already scheduled?

(we can use DBMS_SCHEDULER too)

The DB we use is 11g release 2.


In other words: what we have is this:
- start transaction
- INSERT (triggering a dbms_job.submit)
- INSERT (triggering a dbms_job.submit)
- INSERT (triggering a dbms_job.submit)
- commit
- 3 jobs run

what we want:
- start transaction
- INSERT
- INSERT
- INSERT
- commit
- 1 job run


(this is simplified, in actual code we have a lot of DB access with 0 or N INSERTs as part of other stored procedures.)

Edited by: xerces8 on Aug 9, 2012 2:36 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points