In Support of DBMS_JOB

DBMS_SCHEDULER – great set of functionality, vastly more powerful and flexible than DBMS_JOB but there is at least one thing that DBMS_JOB is still best suited for and that is why DBMS_JOB is still not, as far as I know, officially deprecated.

The documentation on DBMS_JOB may say that it’s only supported for backward compatibility but there’s one massive, great thing that it overlooks – transaction control.

And it’s a point that is quickly forgotten on the forums for example when someone is often leapt upon for even suggesting DBMS_JOB (and probably rightly so 93% of the time).

You might as part of a wider transaction want to submit a piece of asychronous work, something that can be fired off in the background, a job.

The oft-cited example is an email, i.e.

start transaction;
do some work;
send a related email;
maybe do some more work;

And the sending of the email should stand or fail with the transaction.

If the transaction completes the email gets sent.

If the transaction fails along the line, the job to send the email gets rolled back along with everything else.

DBMS_SCHEDULER is not suitable for this.

Yes, it’s vastly more powerful and flexible than DBMS_JOB but it has a flaw – the call to DBMS_SCHEDULER commits.

So, not only does that mean that in the example mentioned, the job to send the email cannot be rolled back but you’ve only just gone and committed your whole transaction when you didn’t want to.

So then you go down the route of using an autonomous transaction to call DBMS_SCHEDULER and so, whilst your wider transaction control hasn’t been violated, that email is going regardless and it’s going just as soon as the scheduler picks it up.

When what you really should be still using is DBMS_JOB.

And I can’t see DBMS_JOB ever going until that oversight in the implementation of DBMS_SCHEDULER is addressed.


Get every new post delivered to your Inbox.

Join 62 other followers