> 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
