Oracle Session Disable Commit in Procedure

This is an interesting feature. This is a very useful feature to test a Oracle procedure without committing the actual changes made by the procedure. If you disable the commit in procedure you will get an error while executing the procedure.

See an example here.

SQL> create procedure xyz is
begin

     delete from emp;
     commit;
end;
/
Procedure created.
SQL> select count(*) from emp;
  COUNT(*)
----------
        8
SQL> alter session disable commit in procedure;

Session altered.

SQL> exec xyz;
BEGIN xyz; END;
*
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "XYZ", line 4
ORA-06512: at line 1
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google