disable commit in procedure

There is an obscure syntax that prevents a procedure from issuing a commit


alter session DISABLE COMMIT IN PROCEDURE;

According to the doc, it prevents procedure from committing your data

Test case

SQL> alter session DISABLE COMMIT IN PROCEDURE
Session altered.
SQL> create table t(x number)
Table created.
SQL> create or replace procedure p is
begin
commit;
end;
Procedure created.
SQL> insert into t values (1)
1 row created.
SQL> exec p
BEGIN p; END;
Error at line 17
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "SCOTT.P", line 3
ORA-06512: at line 1

But some sys procedures may bypass this restriction

SQL> exec dbms_stats.gather_table_stats(user, 'T')
PL/SQL procedure successfully completed.
SQL> rollback
Rollback complete.
SQL> select * from t

X
----------
1

The row was silently committed.

Published by Laurent Schneider

Oracle Certified Master

Join the Conversation

3 Comments

  1. I found this command interesting for testing procedures, it gives the opportunity to check data giving opportunity to rollback, without removing manually the commits from procedeure 🙂

Leave a comment

Your email address will not be published.