Quick Tip: Oracle - Commit on Exit

Oracle by default does not automatically commit a transaction (Autocommit is typically off). Transaction Management (Commit or Rollback) is left to the client as this askTom post explains it nicely. The tool you use may provide this option for you. For e.g., SQL*Plus commits any open transaction upon exit. So, even if you don't have COMMIT at the end of a script, when SQL*Plus will issue a COMMIT when the SQL is exited.

This was the default behavior for a long time and was generally accepted. Some developers wanted to have more control over it, and according to this stackoverflow post there was even a bug (633247) opened for it in 1998! Recently, in Oracle 11g this has finally been changed. Now, as of Oracle 11g R2, the user actually has an option (EXITCOMMIT) to tell SQL*Plus whether to COMMIT or ROLLBACK upon EXIT. Following statement makes SQL*Plus roll back a transaction upon exit.

set exitcommit off

See this Oracle Post for a good example.

Notes:

• One poster in the Stackoverflow post above, actually mentions about Autocommit option in SQL*Plus. I just want to clarify:

SQL*Plus does have an option to set Autocommit on or off. This is actually meant for every statement that you issue, not just the last statement before exit. Prior to Oracle 11gR2, this did not have an impact on the Commit on Exit!

• Also, this change is actually in client tool (SQL*Plus) not in the database. So, if you are using an older client (like I am), you will be disappointed not to find this option!

References

http://stackoverflow.com/questions/1368092/why-does-sqlplus-commit-on-exit

http://www.oracle.com/technetwork/articles/sql/11g-misc-091388.html

http://www.acehints.com/2011/07/oracle-11g-r2-sqlplus-set-exitcommit.html

http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:314816776423