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:
- On load, after a page finishes rendering
- On submit, before branching to another page
- On submit, if one or more validations fail, before re-rendering the page
- After a PL/SQL process that contains one or more [changes to session state specified using] bind variables has completed
- After a computation
- When APEX_UTIL.SET_SESSION_STATE is called [with the
p_commit parameterset or defaulted to
true, where the existing session state value is different to
- When APEX_MAIL.PUSH_QUEUE is called
- When a background process is created using
dbms_scheduler.create_jobor executed by
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.
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.
I rarely write explicit commits anymore, not for day-to-day APEX development.
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).
Cheers, thanks for all your helpful comments.