5 Replies Latest reply on Mar 30, 2020 9:04 PM by jacknomiz

    Explicit commit in custom plsql process

    jacknomiz

      Hi, I'm using Apex 5.1.2, going to 19.1 soon.

       

      My question is about an explicit commit inside a custom plsql process in Apex.

      Do I need an explicit commit, or Apex issues commit implicitly?

       

      Thanks in advance

        • 1. Re: Explicit commit in custom plsql process
          fac586

          jacknomiz wrote:

           

          Hi, I'm using Apex 5.1.2, going to 19.1 soon.

           

          My question is about an explicit commit inside a custom plsql process in Apex.

          Do I need an explicit commit, or Apex issues commit implicitly?

          It depends. APEX issues implicit commits at the following points in page processing:

           

          1. On load, after a page finishes rendering
          2. On submit, before branching to another page
          3. On submit, if one or more validations fail, before re-rendering the page
          4. After a PL/SQL process that contains one or more [changes to session state specified using] bind variables has completed
          5. After a computation
          6. When APEX_UTIL.SET_SESSION_STATE is called [with the p_commit parameter set or defaulted to true, where the existing session state value is different to p_value]
          7. When APEX_MAIL.PUSH_QUEUE is called
          8. When a background process is created using dbms_scheduler.create_job or executed by dbms_scheduler.run_job.

           

          As a result of this, explicit commits inside PL/SQL processes are rarely necessary, as it is assumed that each show/accept request constitutes a transaction. A common problem is having accept requests that execute more than one process, where session state changes unexpectedly commit changes made by earlier processes and the failure of a subsequent process results in an incomplete logical transaction.

           

          It's unlikely that the process requires an explicit commit, but without knowing what it does—and more importantly whether other processes are run before and after—we really cannot say one way or the other.

          • 2. Re: Explicit commit in custom plsql process
            John_K

            Also bear in mind that if you are storing your procedures in the database rather than coding all the logic directly into APEX (which is generally the preferred option) then you limit re-use somewhat if you have implicit commits. For example, if you have a procedure create_absence() held in a package and called from APEX which commits, then you can't (shouldn't) then have a new procedure create_absence_and_update_calendar() because the first procedure commits mid-transaction, so if the latter fails you're in an inconsistent state.

            • 3. Re: Explicit commit in custom plsql process
              Scott Wesley

              I rarely write explicit commits anymore, not for day-to-day APEX development.

              • 4. Re: Explicit commit in custom plsql process
                Mike Kutz

                I've seen problems caused when APEX decides to "rollback and tries again".

                 

                An explicit commit within the Process (along with the "rollback and try again") caused duplicate data in the table (and/or send duplicate emails).

                 

                This happened mostly on misconfigured APEX 4.2 installations (on 11g XE).

                 

                My $0.02

                 

                MK

                • 5. Re: Explicit commit in custom plsql process
                  jacknomiz

                  Cheers, thanks for all your helpful comments.