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;
commit;

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.

About these ads

4 Responses to In Support of DBMS_JOB

  1. Ronald Rood says:

    Hi Dom,
    nice point you make here.
    It comes down to: you can not just exchange the calls to dbms_job for those to dbms_scheduler. If you want to fully utilize the benefits of the Oracle Scheduler, a redesign is advised, in many cases.
    In your case, Lightweight jobs might be of help.
    Check Example 28-2 Creating a Set of Lightweight Jobs in a Single Transaction

    with kind regards,
    Ronald

    • Dom Brooks says:

      Thanks for commenting Ronald, aka Mr Scheduler :)

      Edit:
      I’ve edited your original comment with the link – for some reason it didn’t come through.

      An interesting link but, without having played with it, the COMMIT_SEMANTICS argument of CREATE_JOBS sounds like it allows you to create multiple jobs in call to DBMS_SCHEDULER. Which is useful.
      But there’s still a COMMIT in there. So, going back to the pseudo-example above, transaction control is still an issue.

  2. Narendra says:

    I hope so. Tom has said exactly same thing at http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:25405608521707#2323186400346063349

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: