Error on commit: SELECT FOR UPDATE specified a join of distributed tables
I have an editable ADF table that display a report by joining data across multiple DB tables. One of the tables is on a remote DB.
I was using a view to abstract the joins , in conjunction with an INSTEAD-OF trigger to handle the updates to the underlying table. Only one of the underlying tables in the join is being updated, the rest provide read-only fields in the report. I have an EO representing the view, and a default VO that I use to render the report.
But on Commit, I get an error saying:
ORA-02060: SELECT FOR UPDATE specified a join of non-collocated tables
Cause: An attempt was made to join tables in different remote databases in a SELECT... FOR UPDATE statement.
My only alternative seems to be to build a materialized view on the local DB against the remote table.
We have concerns related to the replication of data here, so is there any other way I could work around this? I have tried local synonyms, and that did not help.