Help: How can I achieve this

[joebayer]

We are using Oracle 9i.

What we want to do is this: When form loads, it will get data from local
Oracle database and remote SQL server database through heterogeneous
connectivity. When user commit update, we want either it succeed to both
databases, or rollback on both databases, to keep the two database
synchronized.

But Oracle donot allow us to do so,
==========================================
SQL> update v1 set id1=9 where id2=4;

1 row updated.

SQL> update v2 set "id2"=5 where "id1"=5;
update v2 set "id2"=5 where "id1"=5
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress

SQL> rollback;

Rollback complete.

SQL> update v2 set "id2"=5 where "id1"=5;

1 row updated.
================================================
Where v2 is a view pointing to a remote table.

Thanks for your help.

[sybrandb]

>From the error manual (you obviously didn't look that up. Could you
explain why you don't do that, and start to cry for help immediately?)

ORA-02047: cannot join the distributed transaction in progress

Cause: Either a transaction is in progress against a remote database
that does not fully support two phase commit, and an update is
attempted on another database, or updates are pending and and an
attempt is made to update a different database that does not fully
support two phase commit.
Action: complete the current transaction and then resubmit the update
request.

My guess is this is a *documented* driver limitation.

--
Sybrand Bakker
Senior Oracle DBA