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. 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 Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>