"Commits are performed when you explicitly issue them or when you alter session state with assignment statements, "select into" queries, or OUT variable assignments to bind-variable notated page- or application-item names or when you call apex_util.set_session_state. If you don't do any of that, no commits happen until the page processing completes."

APEX commits:

• At the end of page processing (ACCEPT processing)
• At the end of page rendering (SHOW processing)
• After session state is changed for page/application items by a page-process
• After User issued 'commit' statement

So a Page Process should look like this to ensure a rollback is performed when a called pl/sql procedure/function fails: begin savepoint savepoint_0; -- your code exception when others then rollback to savepoint_0; raise; end; Note: The savepoint_0 construct is used to ensure that we rollback to the correct place. Otherwise, we would not notice if another commit was done somewhere in the code (because we would roll back to that one, not to savepoint_0). Now, if another commit was done somewhere in the code and we try to roll back to savepoint_0, we will get "ORA-01086 - savepoint 'string' never established in this session or is invalid", which is correct here.

Source: https://forums.oracle.com/forums/thread.jspa?messageID=4221969
Source: https://forums.oracle.com/forums/thread.jspa?threadID=993639
Source: https://forums.oracle.com/forums/thread.jspa?threadID=708781&tstart=0

Dan McGhan's article "Implicit Commits in APEX" can be found here: http://www.danielmcghan.us/2012/08/implicit-commits-in-apex.html
His list of Apex Commit points:

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 bind variables has completed.
5. After a computation.
6. When APEX_UTIL.SET_SESSION_STATE is called.
7. When APEX_MAIL.PUSH_QUEUE is called.

The article contains some excellent background information on this topic, worth a read.


And yet another good article: http://lostlearnings.blogspot.nl/2013/11/commitment-commits-in-apex.html
His conclusion:

• Changing a session variable causes APEX to take a commit. [...] If we move the same value into a session variable, if the value does not change, then APEX does not take a commit.

A very interesting article titled "APEX_UTIL.set_session_state may or may not commit" by Jeff Kemp: https://jeffkemponoracle.com/2014/02/20/apex_util-set_session_state-may-or-may-not-commit/

[...] APEX_UTIL.set_session_state will issue a COMMIT – but only if the value of the item is changed. If you happen to call set_session_state with the value that the item already has, it does nothing, and does not COMMIT. [...]

Related to the articles above: After a page/form submit, you might see something like this in the Debug Message Data:

Session State: Save form items and p_arg_values
...Session State: saved "P1082_SEARCH_PATTERN_1" new value="" (deferred commit)
...Session State: Save "P1082_SEARCH_PATTERN_2" - saving same value: "Sunshine"
...Session State: commit previous changes

So the new value for "P1082_SEARCH_PATTERN_1" is not committed! I have been caught out by this a couple of times now; it's hard to spot and even harder to debug...

A workaround (because I have still not found the root cause, grrr): Add a hidden page item to the page, place it in the first region of the page, and make sure its ID is smaller than all others. This item will "absorb" the deferred commit, so that the next items get their correct values. Not elegant but it works.
For example, on pages 1063, 1071, 1072 and 1082 of this site I needed this rather ugly solution.

If you need to check if a commit has occurred at some point during execution of your code, you can use something like this function: create or replace function commit_has_occurred return boolean is v_n pls_integer; begin select count(*) into v_n from v$session a , v$sqlarea b , v$transaction c where ((a.sql_address = b.address and a.sql_hash_value = b.hash_value) or (a.prev_sql_addr = b.address and a.prev_hash_value = b.hash_value)) and c.ses_addr = a.saddr and sid = ( select sys_context ('USERENV', 'SID') from dual ) ; if v_n = 0 then return true; else return false; end if; end commit_has_occurred;
The function will return true if there are no pending (i.e. non-committed) transactions within the session.
So a test run to use this, would look like this:

• Issue a commit - or otherwise make sure that within the current session, only your test run creates transactions.
• Create a transactions, e.g. an insert.
• Run the suspect (i.e. possibly committing) code you want to check.
• Call the function above - if it returns true then no transactions are pending and therefore the suspect code did indeed commit.

The user executing this function, needs the following grants: grant select on v_$session to tedstruik; grant select on v_$sqlarea to tedstruik; grant select on v_$transaction to tedstruik;