4 Replies Latest reply: Dec 31, 2012 11:40 AM by crimson.sails RSS

    Error on commit: SELECT FOR UPDATE specified a join of distributed tables

    crimson.sails
      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.