If a session does ALTER SESSION DISABLE COMMIT IN PROCEDURE then a procedure tries to do a COMMIT or ROLLBACK, Oracle returns an ORA-00034. You can see what I mean in the example below, which was tested on Oracle 11.2:
SQL> create or replace procedure commit_proc as
SQL> create or replace procedure commit_proc as
2 begin
3 commit;
4 end;
5 /
Procedure created.
SQL> create or replace procedure rollback_proc as
2 begin
3 rollback;
4 end;
5 /
Procedure created.
SQL> exec commit_proc();
PL/SQL procedure successfully completed.
SQL> exec rollback_proc;
PL/SQL procedure successfully completed.
SQL> alter session disable commit in procedure
2 /
Session altered.
SQL> exec commit_proc();
BEGIN commit_proc(); END;
*
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "ANDREW.COMMIT_PROC", line 3
ORA-06512: at line 1
SQL> exec rollback_proc;
BEGIN rollback_proc; END;
*
ERROR at line 1:
ORA-00034: cannot ROLLBACK in current PL/SQL session
ORA-06512: at "ANDREW.ROLLBACK_PROC", line 3
ORA-06512: at line 1
SQL> alter session enable commit in procedure
2 /
Session altered.
SQL> exec commit_proc();
PL/SQL procedure successfully completed.
SQL> exec rollback_proc;
PL/SQL procedure successfully completed.
SQL>
