Re: Turning off DML commit when session is exited or disconnected

From: ddf <oratune_at_msn.com>
Date: Thu, 18 Dec 2008 06:04:40 -0800 (PST)
Message-ID: <7175c3c6-a73a-4f19-8026-d4aa96a6d878@f11g2000vbf.googlegroups.com>

Comments embedded.
On Dec 18, 5:28 am, oracle user <rcl..._at_gmail.com> wrote:
> If i issue a DML statment in oracle database from sqlplus session
> (where autocommit is off by default ) and then 'exit' or 'disc' the
> session without explicitly commiting the DML ,the DML gets commited .

Sorry, you DO commit the DML as, by default, an 'exit' is actually an 'exit commit'. You do have the option to execute an 'exit rollback' instead:

SQL> --
SQL> -- Create a test table
SQL> --
SQL> create table mytest (mykey number,
  2               myval varchar2(40),
  3               constraint mytest_pk
  4               primary key(mykey)

  5 );

Table created.

SQL>
SQL> --
SQL> -- Insert data we don't want to preserve
SQL> --
SQL> insert all

  2 into mytest
  3 values (1, 'Test 1')
  4 into mytest
  5 values (2, 'Test 2')
  6 into mytest
  7 values (3, 'Test 3')
  8 into mytest
  9 values (4, 'Test 4')
 10 into mytest
 11 values (5, 'Test 5')
 12 select *
 13 from dual;

5 rows created.

SQL>
SQL> --
SQL> -- Prove the insert succeeded
SQL> --
SQL> select * from mytest;

     MYKEY MYVAL
---------- ----------------------------------------
         1 Test 1
         2 Test 2
         3 Test 3
         4 Test 4
         5 Test 5

SQL>
SQL> --
SQL> -- Exit session and discard data changes

SQL> --
SQL> exit rollback
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

c:\sql\orcl\examples>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Dec 18 08:02:51 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect #######/^^^^^^^^^^^^^^^^^

Connected.

SQL> set echo on
SQL> @rollback_on_exit_2
SQL> --
SQL> -- Verify the inserts from the previous
SQL> -- session weren't preserved by the 'exit'
SQL> --
SQL> select * from mytest;

no rows selected

SQL>
> Is it possible to turn off this default behaviour.

Sort of. See above.

> Thanks in Advance

David Fitzjarrell Received on Thu Dec 18 2008 - 08:04:40 CST