apps-infra

    Forum Stats

  • 3,874,065 Users
  • 2,266,670 Discussions
  • 7,911,715 Comments

Discussions

DBMS_SCHEDULER.CREATE_JOB Remove Implicit Commit

gdanby
gdanby Member Posts: 221 Bronze Badge
edited Jun 2, 2016 3:49AM in Database Ideas - Ideas

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.

gdanbySven W.FatMartinRctriebGregVAman....Rainer Stenzelabhinivesh.jain2617948Hemant K ChitaleChris HuntsensoftulohmannSentinelDonovanSMWRA EnQualfac586Tony AndrewsScott WesleyJeffrey KempKim Berg HansenChristian Neumueller-OracleAlbert Nelson A3372911Christian Erlingerblessed DBAUser_VJM2Duser6588997user4665933034114user8244334User_NITZAUser_BA87Dconnor_mc_d-OracleBilly VerreynneOren NakdimonScott SwankAlt-064Denis AxlerTimHallPaul MooreAlex NuijtenDom Brooksuser7111641MortenBratenUser_CBCVIGalo BaldaToddBarry3162716Raymond ANiceTheoryVidarMartin Preisscrokuser10206515YuriAPStew AshtonSKartaevGinoThysPeter HraškoUser_671A9KFxChris Clarkehpanchmauser1001802user11157696esoldatovjmitchellDaveDraker_h_smith2Oliver AMarcel HoefsGerhardvillaOraFlado, too1660830Christian OTNPaoloMperecdUser_A4BSUdba_mmUser_ZUPCKUser_EHQOSJose Carlos PavónRogerTjflackMarwimsdstuberJeff Kemp-OracleUser_G3UV3Mohamed HouriNiels HeckerUser_L0MY5Göran PauesJoe Kerrben_gTamas LaczkoYevonglenm
100 votes

Active · Last Updated

«134

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    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
    FatMartinR Member Posts: 190 Blue Ribbon

    Just make it an optional parameter.   Simple!

  • gdanby
    gdanby Member Posts: 221 Bronze Badge

    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.
    Sven W. Member Posts: 10,559 Gold Crown

    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
    GregV Member Posts: 3,106 Gold Crown

    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.
    Sven W. Member Posts: 10,559 Gold Crown

    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

    User_BA87D
  • GregV
    GregV Member Posts: 3,106 Gold Crown

    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
    gdanby Member Posts: 221 Bronze Badge

    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.

    User_BA87D
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

    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
    Jeffrey Kemp Member Posts: 193 Bronze Badge

    Would it be feasible to just use Oracle AQ instead?

apps-infra