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.

3 thoughts on “disable commit in procedure

  1. Cristian

    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 🙂

Comments are closed.