Home » SQL & PL/SQL » Client Tools » When does exiting Sqlplus not commit an insert statement? (Oracle 9.2.2, Linux)
When does exiting Sqlplus not commit an insert statement? [message #378270] Mon, 29 December 2008 11:54 Go to next message
efachim
Messages: 42
Registered: July 2008
Member
Hi Folks,

Has anyone come across a scenario where following an insert statement, you log out of sqlplus without an explicit commit statement, and then find that the inserted data was not committed? Contrary, to general consensus, which is that exiting sqlplus automatically commits data, it would seem that there might be a way to set up your environment so that exiting sqlplus does not automatically commit your data. Is this true?

TIA,

Efachim
Re: When does exiting Sqlplus not commit an insert statement? [message #378271 is a reply to message #378270] Mon, 29 December 2008 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of ./fa/1597/0/, copy and paste what you did.
Before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: When does exiting Sqlplus not commit an insert statement? [message #378281 is a reply to message #378270] Mon, 29 December 2008 14:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
By default, EXIT (or QUIT) with no additional clauses exits SQL*Plus and commits changes. However, if you
EXIT ROLLBACK
changes will be rolled back (I have removed unnecessary lines):
SQL> create table test as select * From dept;

Table created.

SQL> select * from test;

    DEPTNO DNAME          LOC
---------- -------------- --------------------------------------------------
        10 ACCOUNTING     New York
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> delete from test where deptno = 40;

1 row deleted.

SQL> exit rollback;
Now let's see what has been done:
c:\temp>sqlplus scott/tiger

SQL> select * from test;

    DEPTNO DNAME          LOC
---------- -------------- --------------------------------------------------
        10 ACCOUNTING     New York
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

As far as I can tell, there's no way to exit SQL*Plus without committing by default. Though, perhaps you have heard of SET AUTOCOMMIT ON or OFF (which has nothing to do with committing or rolling back changes upon exiting SQL*Plus).
Re: When does exiting Sqlplus not commit an insert statement? [message #378329 is a reply to message #378281] Tue, 30 December 2008 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quit the session in which sqlplus runs and nothing will be committed.
Example in windows (in *nix it's even more obvious):
click start-button and select run. enter the full path to sqlplus.exe
connect and insert a row into a table.
click close-button in top-right corner of the window and voila!

[Updated on: Tue, 30 December 2008 00:56]

Report message to a moderator

Re: When does exiting Sqlplus not commit an insert statement? [message #380395 is a reply to message #378329] Sun, 11 January 2009 17:50 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I think there may be multiple issues here:

1) in the old days, the default behaviour of SQLPLUS was: if you exit without commit, it would rollback.

2) at some point in time (mid 90's?), Oracle in its wisdom, decided to change this behaviour so that when you exit from SQLPLUS, you automatically commit (who knows why (I preffered the rollback behaviour myself)).

3) as has been demonstrated, it is possible to explicitly say rollback when you exit.

4) additionally, as I remember it, there is also a parameter somewhere to revert sqlplus back to the older rollback behaviour if desired.

5) complicating mattters further, oracle processes can in many situations recognize the difference between a controled exit of SQLPLUS, and a session that dies unexpectedly. Thus if your SQLPLUS program quits on you (ie. dies) a rollback will be done when the oracle processes recognize this. Thus a SQLPLUS session that ends without an explicit commit/rollback can go either way depending upon if the session ended by user request or not.

6) Lastly, it is also possible to exit SQLPLUS by request but return an error code. It is up in the air for me what happens when you exit with an error code. Maybe someone can fill us on this one.

Of course, the years are not always kind, with experience comes the ability to forget what we have learned. Maybe someone else remebers otherwise...

Kevin

[Updated on: Sun, 11 January 2009 17:52]

Report message to a moderator

Previous Topic: How to make new Database connection through the SQL Developer
Next Topic: ORA-01795: maximum number of expressions in a list is 1000
Goto Forum:
  


Current Time: Fri Dec 09 03:53:22 CST 2016

Total time taken to generate the page: 0.09235 seconds