DBMS_SCHEDULER.CREATE_JOB Remove Implicit Commit

blessed DBA

Then You can go for a Application server Scheduler  So it will be same like transaction

user466593

I have implemented this using event-based scheduler jobs. https://docs.oracle.com/html/E25494_01/scheduse005.htm

But it is more complicated than just creating one job.

GregV

I have implemented this using event-based scheduler jobs. https://docs.oracle.com/html/E25494_01/scheduse005.htm

But it is more complicated than just creating one job.

There's no need for that. The commit/rollback capability has been replaced with the "enabled" paramater when you create a job. I'm still waiting for a concrete example where an explicit commit would be useful when creating a job through dbms_scheduler.

connor_mc_d-Oracle

There's no need for that. The commit/rollback capability has been replaced with the "enabled" paramater when you create a job. I'm still waiting for a concrete example where an explicit commit would be useful when creating a job through dbms_scheduler.

eg Function "BUY_PRODUCT" is
- insert a new row into SALES table

- if that sale was more than $10,000, send an SMS alert to our audit team

- do some more queries and DML as part of this transaction

I don't want to send that SMS inline with the code because SMS/network comms etc is way slower than a db insert, so I'll do it asynchronously

Now I'm in a bind

- if I call the DBMS_SCHEDULER to create a job send that SMS, I've committed which breaks the transaction model for subsequent DML - I've now got a corruption

- if I move the scheduler part to the end of the transaction, I'm lowering that risk, but I've got app changes to make, and what if the call to create the scheduler job fails... I've committed but will never send an SMS

- Ideally I want "sending an SMS" to be transactional (or as close to it as it can be). Using DBMS_JOB solves this - I submit the job, but it only really comes into existence when the parent transaction commits.

Granted I could re-architect this with AQ, but if I've *currently* got code that relies on DBMS_JOB to do this, the ideal fix would be a DBMS_SCHEDULER drop-in replacement.

GregV

eg Function "BUY_PRODUCT" is
- insert a new row into SALES table

- if that sale was more than $10,000, send an SMS alert to our audit team

- do some more queries and DML as part of this transaction

I don't want to send that SMS inline with the code because SMS/network comms etc is way slower than a db insert, so I'll do it asynchronously

Now I'm in a bind

- if I call the DBMS_SCHEDULER to create a job send that SMS, I've committed which breaks the transaction model for subsequent DML - I've now got a corruption

- if I move the scheduler part to the end of the transaction, I'm lowering that risk, but I've got app changes to make, and what if the call to create the scheduler job fails... I've committed but will never send an SMS

- Ideally I want "sending an SMS" to be transactional (or as close to it as it can be). Using DBMS_JOB solves this - I submit the job, but it only really comes into existence when the parent transaction commits.

Granted I could re-architect this with AQ, but if I've *currently* got code that relies on DBMS_JOB to do this, the ideal fix would be a DBMS_SCHEDULER drop-in replacement.

Hi Connor,

As you said, the thing is to move the DBMS_SCHEDULER.create_job at the end of the code. I'm trying to think what would be the reason for a create_job to fail? Even if you specify an invalid pl/sql block or stored procedure as the job_action (either for a plsql_block or stored_procedure job_type), the job's creation will still succeed. And if you specify a wrong value for a parameter, for example job_type => 'procedure', the job's creation will fail, but no commit will happen.

That being said, I must admit there's one useful situation when the job's creation should be transactionnal :when it's part of a trigger's logic. The job is called by a trigger and should be created only if the transaction commits. In that particular situation I agree there should be an option to prevent DBMS_SCHEDULER from committing.

TimHall

Since the introduction of the DBMS_SCHEDULER in Oracle 10g, people have been asking for some form of commit flag to be introduced. The use of DBMS_JOB to decouple processes has a long history. The fact that this was no longer possible with DBMS_SCHEDULER was a mistake IMHO. Several people, including me, have raised enhancement requests for this functionality.

We aren't asking for this because we don't know alternative ways to code it in Oracle. See:

https://oracle-base.com/articles/misc/decoupling-to-improve-performance

We aren't asking for this because we are unaware of other schedulers. We are asking for it because it's a feature we used to have that's been taken away, and it was really useful. The fact you can code it a different way, or some people never use this method is irrelevant. Having a transactional job scheduler is important, which is why people still use DBMS_JOB for this purpose.

Possibly worth people reading this post by Connor.

https://connor-mcdonald.com/2019/05/28/dbms_job-the-joy-of-transactions/

It's interesting that Oracle have implemented DBMS_JOB in Oracle 19c using DBMS_SCHEDULER, yet still maintained the transactional nature of DBMS_JOB. This proves it's really easy to do!

With that in mind, give it us in DBMS_SCHEDULER directly please!

Cheers

Tim...

user7111641

Hello All,

Allowing the DBMS_SCHEDULER to be transactional , based on a parameter, is a top essential feature,
if Oracle does indeed encourage the idea to have it replace completely the DBMS_JOB functionality.

Submitting a job as part of a business transaction is very legitimate and many developers have probably used this feature
to implement essential functionality in their applications.

So, it is essential to have this feature available, for ALL types of DBMS_SCHEDULER jobs, not just for lightweight jobs.

After such a long time since this package was first introduced, it is surprising that this enhancement has not yet been implemented.

Thanks a lot & Best Regards,
Iudith Mentzel

MortenBraten

I will add this comment to say that I strongly support this idea. It should be possible to submit a job without an implicit commit, in other words, if dbms_job is to be deprecated (which it now has been in Oracle 19c) and removed altogether sometime in the future, then Oracle must provide a way to achieve the same (ie "transactional job creation") via the new API.

Come on, Tom Kyte said this 13 years ago, and it's way overdue... !

https://asktom.oracle.com/pls/apex/asktom.search?tag=dbms-scheduler-emailing-after-inserting-into-database-tables#577780…

- Morten

FatMartinR

In recognition of the legitimacy of the technique, how about an official way to de-couple rather than the DBMS_JOB hack?

EXEC  DBMS_UTILITY.INCLUDE_IN_TRANSACTION (pl_sql code goes here);     

And it only gets executed upon a COMMIT.

Jeffrey Kemp

For those watching you may find this proof of concept of interest, borrowing the idea of javascript promises: https://github.com/morten-egan/promises_ninja