Re: How to trap a commit from an application?

dean wrote:
> Is there any way I can say to Oracle (9.2 or 10g) that if an
> application attempts to call commit, then throw an exception? An on-
> commit event?

You can fake it with the following Big Time Kludgy Hack, that takes advantage of the rollback behavior of DEFERRABLE constraints, described below. This will not prevent "rogue" COMMITs from 3rd parties from being issued, but it will prevent their effects, which may be good enough for you.

(1) Create a table called COMMIT_LOCKER:

      create table commit_locker ( n number not null deferrable );

(2) In your transaction do:

      alter session set constraints = deferred;
      insert into commit_locker values(null);
      ... now do more INSERT/UPDATE/DELETE (w/out COMMIT)

(3) Now the session is "booby trapped" in that any COMMIT will

   roll back all changes due to the failure of the NOT NULL on    COMMIT_LOCKER.N (4) Once YOU are satisfied that COMMIT is OK to do, issue:

      delete from commit_locker;
      commit;

This seems to work for me in the face of any "pre-committing" commands like TRUNCATE TABLE, etc.

Note that at the start of step (4) you have the option of inspecting the result of

      SELECT count(*) FROM commit_locker

which if it has one row means nobody went and tried a commit, and which if it is empty means something did a commit and rolled everything back. This knowledge may be useful to decide whether to do additional "cleanup" stuff.

But my little "there has GOT to be a better way" light is blinking. ;)

• JH