PLSQL procedure statement level rollback

script_statement_level_rollback.sql

spool spool_statement_level_rollback.log 

cl scr 
set echo on 
set time off 

drop table slr_test purge;

create table slr_test(id int primary key);

create or replace procedure slr_insert_no_rollback as 
begin 
    insert into slr_test values (2);
    insert into slr_test values (3);
    insert into slr_test values (3); -- this one throws ORA-1
end slr_insert_no_rollback;
/

create or replace procedure slr_insert_rollback as 
begin 
    slr_insert_no_rollback;
exception when dup_val_on_index then 
    rollback; -- this one rolls back the entire transaction, including (1)
    raise;
end slr_insert_rollback;
/

insert into slr_test values (1); 

call slr_insert_no_rollback();

-- (1) is still there since only (2) and (3) were implicitly rolled back
select * from slr_test;

call slr_insert_rollback();


-- (1) is NOT there, as the proc performed the rollback
select * from slr_test;

drop procedure slr_insert_no_rollback;
drop procedure slr_insert_rollback;
drop table slr_test purge;

spool off

exit

spool_statement_level_rollback.log

00:32:41 SQL> set time off
SQL> 
SQL> drop table slr_test purge;
drop table slr_test purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.00
SQL> 
SQL> create table slr_test(id int primary key);

Table created.

Elapsed: 00:00:00.01
SQL> 
SQL> create or replace procedure slr_insert_no_rollback as
  2  begin
  3      insert into slr_test values (2);
  4      insert into slr_test values (3);
  5      insert into slr_test values (3); -- this one throws ORA-1
  6  end slr_insert_no_rollback;
  7  /

Procedure created.

Elapsed: 00:00:00.04
SQL> 
SQL> create or replace procedure slr_insert_rollback as
  2  begin
  3      slr_insert_no_rollback;
  4  exception when dup_val_on_index then
  5      rollback; -- this one rolls back the entire transaction, including (1)
  6      raise;
  7  end slr_insert_rollback;
  8  /

Procedure created.

Elapsed: 00:00:00.00
SQL> 
SQL> insert into slr_test values (1);

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> call slr_insert_no_rollback();
call slr_insert_no_rollback()
     *
ERROR at line 1:
ORA-00001: unique constraint (LKU.SYS_C0012603) violated
ORA-06512: at "LKU.SLR_INSERT_NO_ROLLBACK", line 5


Elapsed: 00:00:00.02
SQL> 
SQL> -- (1) is still there since only (2) and (3) were implicitly rolled back
SQL> select * from slr_test;

        ID
----------
         1

1 row selected.

Elapsed: 00:00:00.00
SQL> 
SQL> call slr_insert_rollback();
call slr_insert_rollback()
     *
ERROR at line 1:
ORA-00001: unique constraint (LKU.SYS_C0012603) violated
ORA-06512: at "LKU.SLR_INSERT_ROLLBACK", line 6
ORA-06512: at "LKU.SLR_INSERT_NO_ROLLBACK", line 5
ORA-06512: at "LKU.SLR_INSERT_ROLLBACK", line 3


Elapsed: 00:00:00.01
SQL> 
SQL> 
SQL> -- (1) is NOT there, as the proc performed the rollback
SQL> select * from slr_test;

no rows selected

Elapsed: 00:00:00.00
SQL> 
SQL> drop procedure slr_insert_no_rollback;

Procedure dropped.

Elapsed: 00:00:00.01
SQL> drop procedure slr_insert_rollback;

Procedure dropped.

Elapsed: 00:00:00.00
SQL> drop table slr_test purge;

Table dropped.

Elapsed: 00:00:00.02
SQL> 
SQL> spool off