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?
>
> The business logic is thus: (1) Begin transaction; (2) Perform large
> refresh operation from alternate data source (access, via staging
> tables in a different schema); (3) Allow user to navigate around the
> application to verify that the data looks sound, and: (4) Then (and
> ONLY then) call a commit if desired.
>
> The problem I have is that there are ddl-procedure calls, truncates,
> etc. and other third-party hard-to-capture sources of commit (grids,
> for example) that are implemented in the application, and which may be
> called accidentally during step 3. I'd rather put a hold on all
> commits at the oracle level, if that's possible.
>
> Thanks for any recommendations.
> -Dean
>
> Oracle 9.2i, 10g, Windows, ADO.

I am going to disagree with Ana C. on this both respect to whether it is possible and whether it is desirable.

Take a look at the capabilities of the built-in LM package. Usually used via its DBMS_WM synonym which performs Workspace Management. What this package allows you to do is to create multiple logical workspaces. Essentially to multiversion data within a single table.

The LIVE workspace would be your real data. You could then create a workspace for the new data. If you don't like the changes they can be rejected. If you do like them you can merge the changes.

The package also has the capability of allowing you to manage conflicts between workspaces on a record-by-record basis. Altogether a great built-in capability.

If you would like to see a demo of how it works go to Morgan's Library at www.psoug.org and scroll down to DBMS_WM Synonym. There is a demo at the bottom of the page you can run.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org