Re: Help: How can I achieve this

joebayer (nospam) wrote:
> 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.

>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