ORA-00034

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

  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>