Forum Stats

  • 3,854,571 Users
  • 2,264,387 Discussions
  • 7,905,728 Comments

Discussions

Categories

DBMS_SCHEDULER.CREATE_JOB Remove Implicit Commit

2

Comments

  • blessed DBA
    blessed DBA Member Posts: 218

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

    · Share on TwitterShare on Facebook
  • user466593
    user466593 Member Posts: 9 Green Ribbon

    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.

    · Share on TwitterShare on Facebook
  • GregV
    GregV Member Posts: 3,096 Gold Crown

    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.

    William Robertson
    · Share on TwitterShare on Facebook
  • GregV
    GregV Member Posts: 3,096 Gold Crown

    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.

    David KurtzNiall
    · Share on TwitterShare on Facebook
  • user7111641
    user7111641 Member Posts: 4 Blue Ribbon

    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

    user1001802PeterG
    · Share on TwitterShare on Facebook
  • MortenBraten
    MortenBraten Member Posts: 304 Silver Badge

    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

    SentinelNiallPeterG
    · Share on TwitterShare on Facebook
  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    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.

    · Share on TwitterShare on Facebook
  • Jeffrey Kemp
    Jeffrey Kemp Member Posts: 193 Bronze Badge

    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

    · Share on TwitterShare on Facebook