Monday, March 03, 2014

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>

No comments: