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

Answer 1

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.

• michel-lind: Thanks, I was afraid that might be the case, though I wonder under what exact circumstance is it triggered (I've tried the same query from Oracle 12 to Oracle 10.2.0.3 and it still triggers 02064). Will probably stay away from MERGE until we can upgrade everything to Oracle 12c