Problem using merge into a remote table over dblink

Question

I'm attempting to perform a merge into a remote table, let's say on server A (over a dblink), using a local table on server B. Source and target databases are both on Oracle 10.2.0.3.

We already have a similar existing merge that works fine (but with the query running on server A and merging into a table on server B) so I'm a bit flummoxed as to what could be causing the problem.

I've reduced the case to the simplest - having identical tables and using the entire content of the local table for the merge, and still the merge fails with ORA-02064 "distributed operation not supported":

MERGE INTO attendance@dblnk tgt
USING (
  SELECT *
  FROM attendance
  -- WHERE TRUNC(in_date) = TO_DATE('01.09.2013', 'DD.MM.YYYY')
) src
ON (tgt.emp_no = src.emp_no AND tgt.in_date = src.in_date)
WHEN NOT MATCHED THEN
  INSERT (emp_no, in_date, out_date)
  VALUES (src.emp_no, src.in_date, src.out_date)

Any idea what I should be checking next? Many thanks in advance

score 2 · Accepted Answer · answered Sep 05 '13 at 05:22

2

This is a bug. (Bug number 4311273).

ORA-2064 using MERGE statement over a database link

... which was fixed in Oracle 10.2.0.4. Upgrade your database software.

answered Sep 05 '13 at 05:22

Philᵀᴹ

31,952
10
86
108

Problem using merge into a remote table over dblink

1 Answers1