OraFAQ Forum: Client Tools » When does exiting Sqlplus not commit an insert statement?

Home » Other » Client Tools » When does exiting Sqlplus not commit an insert statement? (Oracle 9.2.2, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

When does exiting Sqlplus not commit an insert statement? [message #378270]

Mon, 29 December 2008 11:54

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

Report message to a moderator

Re: When does exiting Sqlplus not commit an insert statement? [message #378271 is a reply to message #378270]

Mon, 29 December 2008 12:14

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Instead of /forum/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

Report message to a moderator

Re: When does exiting Sqlplus not commit an insert statement? [message #378281 is a reply to message #378270]

Mon, 29 December 2008 14:22

Littlefoot
Messages: 21807
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).

Report message to a moderator

Re: When does exiting Sqlplus not commit an insert statement? [message #378329 is a reply to message #378281]

Tue, 30 December 2008 00:53

Frank
Messages: 7901
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

Kevin Meade
Messages: 2103
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 10:48:03 CDT 2024