Skip to Main Content
  • Questions
  • DBMS_JOB deprecated in 12c Release 2 in favor of DBMS_SCHEDULER

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Kurt.

Asked: December 01, 2016 - 5:05 pm UTC

Last updated: February 28, 2018 - 2:10 am UTC

Version: 12c Release 2

Viewed 1000+ times

You Asked

The 12c2 database upgrade guide ( http://docs.oracle.com/database/122/UPGRD/deprecated-features-oracle-database-12c-r2.htm#UPGRD-GUID-C34B4093-97BE-4237-9BE4-F45450F23BA3 ) identifies DBMS_JOB as deprecated and notes that it may be desupported in a future release.
Further, it recommends that developers move to DBMS_SCHEDULER.

I use DBMS_JOB to send emails because it requires an explicit commit to actually start the job, whilst DBMS_SCHEDULER commits the LUW when its called (at least, that is the way it has worked in the past and I don't see anything in the 12c2 docs that contradicts this).

Assuming I've interpreted this correctly, how can one create a scheduled job that is transaction-safe? AQ? Pipes? Will DBMS_SCHEDULER be extended to let the calling program handle commit logic?

Thanks!

and Connor said...

Good point - that's the first I've seen that deprecation notice, and I agree - I find a lot of value in DBMS_LOB's transactional nature.

I'll ask around and see what I can find out.

In the mean time - I'd stick with DBMS_JOB. Given that 12.2 is only just out, I imagine it wont be going away any time soon.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!

A reader, December 02, 2016 - 1:05 pm UTC

Thanks for addressing this. It will be interesting to see what comes of it.

Anything new?

Marcus, February 22, 2018 - 7:40 am UTC

Have you found a solution for transactional jobs?
Connor McDonald
February 23, 2018 - 12:33 am UTC

Nope.

DBMS_JOB is still there in 18c.

Workaround

Racer I., February 23, 2018 - 10:55 am UTC

Hi,

Maybe something like this is possible : Add your own table where you store which jobs should run. Inserting the job-ID from the scheduler-start (I assume there is one) in your own transaction.

The job would only actually execute if its ID is in that table (and delete it at the end). It would need some grace period of retries waiting for the commit.

regards,
Connor McDonald
February 24, 2018 - 1:21 am UTC

It would need some grace period of retries waiting for the commit.

Yeah, that's the kicker.

I'd strongly suggest people go to

https://community.oracle.com/community/database/database-ideas

and log a request for DBMS_SCHEDULER to support a transactional extensions (perhaps for just plsql jobs)

deprecated

A reader, February 27, 2018 - 9:20 pm UTC

I wouldn't worry too much about deprecation.

Varchar datatype (as opposed to varchar2) has been deprecated as long as I've been using Oracle (more than 20 years), and it hasn't gone anywhere yet.
Connor McDonald
February 28, 2018 - 2:10 am UTC

Thats generally not a good game to play :-)


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here