Re: Transactional haywire...

> Does anyone know a way to implement following scenario in a single
> transaction:
>
> From a VB application we connect to Oracle over OleDB to execute a
> couple of stored procs in following way:
>
> - Application goes to the DB as user uA executing procedure pA
> - Application goes to the DB as user uB executing procedure pB
> - Commit or rollback (all or nothing)
>
> pA does a couple of things in the DB that allow pB later on to delete
> data without being faced with foreing key constraints, so it's not an
> option to have pA not commit because pB needs to be able to see the
> results of pA while being logged to Oracle under another user account.

No way in a single transaction -- for the reason you stated yourself: uA must commit, before uB does its stuff, because there is no way uB und uA can share a transaction.
So, your question then translates to "How to undo a committed transaction?"
Prior to 9i, you have no chance. In 9i and above, you may look at the flashback query feature to show you the table(s) as they were when pA started. This should allow you to reverse the changes if need be. But of course this assumes that you are still in control (e.g., the instance hasn't crashed meanwhile) after pB finishes, in which case you probably don't want to "rollback" anyway. So sorry, bad luck -- no automatic rollback for you.

You can design and implement some kind of homemade two-phase commit mechanism, but you will definitely run into inconsistencies sooner or later.
Or you can collect pA's changes instead of applying them directly, and then have pB apply them in uB's transaction, but using uA's rights (by calling a definer's rights stored procedure owned by uA). But I fail to see how is that different from calling pA in uB's session.

So, in your case, I would question the reasons you don't want to elaborate on. It's hard to imagine *valid* reasons for such a constraint.

Have a nice day,
Flado Received on Wed Feb 04 2004 - 16:51:15 CET