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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google