DBMS_SCHEDULER.CREATE_JOB Remove Implicit Commit

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

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.

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.

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

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.