Oracle Session Disable Commit in Procedure

This is an interesting feature. This is a very useful feature to test a Oracle procedure without committing the actual changes made by the procedure. If you disable the commit in procedure you will get an error while executing the procedure.

See an example here.

SQL> create procedure xyz is

begin

     delete from emp;
     commit;
end;
/
Procedure created.
SQL> select count(*) from emp;
  COUNT(*)
----------
        8
SQL> alter session disable commit in procedure;

Session altered.

SQL> exec xyz;

BEGIN xyz; END;
*
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "XYZ", line 4
ORA-06512: at line 1