PL/SQL block abnormal termination

In OTN discussion forum it has been asked when a PL/SQL block got terminated abnormally what will happen?

couple of experiments have been done on this issue to understand it clearly.

C:\> sqlplus scott/tiger

SQL> select * from dept;


DEPTNO DNAME LOC
---------- -------------- -------------
50 IT LONDON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Table dept1 has the structure as that of dept.

SQL> ed
Wrote file afiedt.buf

1 declare
2 begin
3 insert into dept1 select * from dept;
4 dbms_lock.sleep(20); -- sleeping for 20 secs
5 insert into dept1 select * from dept;
6 dbms_lock.sleep(20); -- sleeping for 20 secs
7 insert into dept1 select * from dept;
8* end;
SQL>/

While the above code was running the session has been terminated abnormally. Immediately the v$session view was checked as a DBA. It has shown active session for user SCOTT. When the block execution finished v$session result in no session for SCOTT. Select * from dept1 result in no rows for the table dept1 when fetched from a new session for SCOTT.

Again the same experiment was done with the following PL/SQL block..

SQL> ed
Wrote file afiedt.buf

1 declare
2 begin
3 insert into dept1 select * from dept;
4 dbms_lock.sleep(20);
5 insert into dept1 select * from dept;
6 dbms_lock.sleep(20);
7 insert into dept1 select * from dept;
8 commit;
9* end;
SQL>/


when fetched from dept1 the following output was found.

SQL> select * from dept1;

DEPTNO DNAME LOC
---------- -------------- -------------
50 IT LONDON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT LONDON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT LONDON

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

15 rows selected.

SQL>

Conclusion: PL/SQL block will continue to execute upon abnormal termination. Only committed execution will be preserved and uncommitted execution will be purged out.