DBMS_SCHEDULER.CREATE_JOB Remove Implicit Commit

gdanby

There should be an option when creating a DBMS Scheduler job to create this as part of the transaction, so it can be rolled back if the transaction fails. DBMS_JOB worked like this and DBMS_SCHEDULER is so much better, except it's not transactional. I know there are workarounds, but these feel a bit messy. It makes migrating from DBMS_JOB to DBMS_SCHEDULER a harder task than it should be.

Sven W.

A scheduled job runs as a background process and is completly independed from the transaction that called it.

As fas as I remember it was the same for DBMS_JOBS.

It is not a matter of "removing some impicit commit". It is a matter of whether the job runs in a separate session or alternatively will block the session that created the job. Which would make the whole concept of a scheduled job pointless.

FatMartinR

Just make it an optional parameter.   Simple!

gdanby

Hi Sven, just to make it clearer. I'm not talking about the actual job running in the same transaction. That will always run in a background session (unless you use the RUN_JOB, setting the appropriate parameter). I'm talking about actually creating the job in the first place.

Sven W.

Hi Sven, just to make it clearer. I'm not talking about the actual job running in the same transaction. That will always run in a background session (unless you use the RUN_JOB, setting the appropriate parameter). I'm talking about actually creating the job in the first place.

Thanks for the clarificarion. Yes I completely misunderstood what you ment.

I remember I troubles with dbms_jobs in the past, that I wanted to start in 5 min. When one forgets to commit, then the job didn't start.

Makes sense if you can have the same behaviour on purpose. As an optional parameter, as FatmartinR  suggested.

GregV

There may be good internal reasons for the DBMS_SCHEDULER.Create_Job prcedure to commit.

I use a lot the DBMS_SCHEDULER package and this implicit COMMIT has never given me any problem.

I cannot think of a reason why you wouldn't want the Create_Job call not to succeed.

If more DML actions are required, then why not do them prior to calling this procedure?

And if somehow the job shouldn't have been created, then just drop it using the drop_job procedure.

Sven W.

There may be good internal reasons for the DBMS_SCHEDULER.Create_Job prcedure to commit.

I use a lot the DBMS_SCHEDULER package and this implicit COMMIT has never given me any problem.

I cannot think of a reason why you wouldn't want the Create_Job call not to succeed.

If more DML actions are required, then why not do them prior to calling this procedure?

And if somehow the job shouldn't have been created, then just drop it using the drop_job procedure.

Maybe you have an open transaction and don't want that this is commited just because some job is scheduled to run at next night.

Having said that, there is also the DBMS_SCHEDULER.CREATE_JOBS command , which is able to start several jobs in a single transaction.

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#BABCIJJA

GregV

Maybe you have an open transaction and don't want that this is commited just because some job is scheduled to run at next night.

Having said that, there is also the DBMS_SCHEDULER.CREATE_JOBS command , which is able to start several jobs in a single transaction.

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#BABCIJJA

Yes but we're talking about the Create_Job here, so you normally create your job just once, why mix it with other DML instructions that you potentially want to rollback?

gdanby

Yes but we're talking about the Create_Job here, so you normally create your job just once, why mix it with other DML instructions that you potentially want to rollback?

It was more for those times when you want to create a job that runs only once, e.g. after validating and loading data into a table, you'd want to then process the data as a background task. This was easy under DBMS_JOB as you'd call it to create the one-off job as part of your transaction. If anything subsequently failed, then the create job would be rolled back. Also, since the create job wouldn't be committed until your transaction commits, there would be no chance of it running before you'd committed your original table changes! There are workarounds as you've said, but just seems that moving these one-off type jobs from DBMS_JOB to DBMS_SCHEDULER would be a lot easier if we had an additional parameter.

Hemant K Chitale

A call to create a job might be part of a transaction with one or more preceding DMLS and succeeding DMLs.  So an auto-commit would break the model.  Yes, I'd like to remove the forced commit.

Jeffrey Kemp

Would it be feasible to just use Oracle AQ instead?