In this post, I am trying to clarify above questions :
SQLPLUS session is exited
-----------------------------------
If a sqlplus session is exited, by default it commits all the pending transactions. By default in sqlplus, EXITCOMMIT is set to ON and this is how it enforces sqlplus to commit any pending transaction!. If you want to disable this feature, set EXITCOMMIT to OFF.
Also please be aware that AUTOCOMMIT also can be set in sqlplus which is OFF by default,so it should not be a concern in normal cases however, if this is set, then any pending transactions are also committed.
Example 1 :
--- Session 1 (Default sqlplus setting) ---
SQL>create table dummy (id number);
Table created.
SQL>insert into dummy values(100);
1 row created.
SQL>exit;
--- Session 2 (Default sqlplus setting) ---
SQL>select * from dummy;
id
----
100
When session1 exited,all pending transactions are committed
and as the result, session 2 can see the change
Example 2 :
--- Session 1 (Disable exitcommit) ---
SQL>set exitcommit OFF
SQL>create table dummy (id number);
Table created.
SQL>insert into dummy values(100);
1 row created.
SQL>exit;
--- Session 2 ---
SQL>select * from dummy;
no rows selected
When session1 exited, all pending transactions are
rolledback if exitcommit is disabled
PLSQL blocks and transactions
------------------------------
Oracle treats stored procedure call as atomic operation.In other words, when begin.... end PLSQL code is submitted, Oracle wraps it with SAVEPOINT and if the block fails in any part, Oracle restores the change before the whole PLSQL call.
Example 3 :
--- Session 1 - PLSQL block ---
SQL>begin
2 insert into dummy values(1000);
3 insert into dummy values(1001,'aa');
4 end;
5 /
ERROR at line 3:
ORA-06550: line 3, column 13:
PL/SQL: ORA-00913: too many values
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
------------------- Session 2 -----------------
SQL>select * from dummy;
no rows selected
The whole PLSQL is an Atomic operation,
So no changes are committed
Release run with many PLSQL blocks
------------------------------------
For a release to run which has several PLSQL blocks and SQL statement, I prefer to commit all transactions as long as there is no issue and when there is an issue, the script stops applying further changes.
The key is not to run the whole script over and over again if an issue is detected.
For this, WHENEVER SQLERROR EXIT SQL.SQLCODE; is used which exits the session when error occurs and at the same time it does commit any pending transaction.
Example 4 :
--- Session 1 - PLSQL block ---
run.sql :
WHENEVER SQLERROR EXIT SQL.SQLCODE;
SET ECHO ON VERIFY ON
begin
insert into dummy values(2000);
end;
/
begin
insert into dummy values(3000,'aa');
end;
/
SQL>@run.sql
ERROR at line 8:
ORA-06550: line 8, column 13:
PL/SQL: ORA-00913: too many values
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored
------------------- Session 2 -----------------
SQL>select * from dummy;
id
---
2000
Each PLSQL is an Atomic operation, when the second
PLSQL fails, the session is exited but as the result
of exit, it does commit all pending transactions including
the first PLSQL
