Gotcha: Accessing Oracle 11g with older SQL*Plus

Oracle 11g has several enhancements over the previous versions. Some of these changes are in the client tools like SQL*Plus. To be able to use these you actually need to upgrade the client tools as well. If you are using old clients like me (I use Oracle 10g SQL*Plus), then you won't see those changes in Oracle 11g. For e.g., the EXITCOMMIT option I mentioned in my earlier post cannot be used in my scripts!

(I couldn't upgrade my client because we use Powerbuilder 10.2 with EA Server 5.5 which doesn't support Oracle 11g drivers. So, until we upgrade those tools, we are stuck with 10g client!)

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