Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / All-Topics

Quick Tip: Oracle – Commit on Exit

5.00/5 (1 vote)
6 Nov 2013CPOL1 min read 10.4K  
Oracle - commit on exit

Introduction

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 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. The following statement makes SQL*Plus roll back a transaction upon exit.

SQL
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

Filed under: CodeProject, Databases, Oracle Tagged: Autocommit, COMMIT, Oracle, Oracle 11g, SQL*Plus

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)