Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 –  michel-slm Sep 5 '13 at 7:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.