Friday, October 5, 2007

MERGE and Database Link Problem

Using 10g R2 database.

When trying to do a MERGE where the target table is on the remote database and the source table is a sub-select on the local database, the following error was raised:

ORA-01008: not all variables bound

The MERGE statement in question had no bind variables.

Furthermore, if I change the target table to an identical local table it works just fine, so it's not a syntax problem.

I searched all over the web and all over technet and but found only a few scant references to this problem. I was unable to find any restriction documented under DISTRIBUTED TRANSACTIONS in the Oracle documentation. I now believe that MERGE will only work over a DB Link when the target is on the local database and the source table is on the remote database.

If you have information about this restriction, please leave a comment.


Ricardo Balieiro said...

Hi, I had a similiar problem, in my case the cursor sharing parameter in the remote database was set to "Force", I thought it could be that, then changed it to "Exact", and it worked fine. But honestly I didn´t quiet understand why it worked out.
If try this out let me know it.

Grouchy said...

This is over a year late, but I also encountered the same problem. I had to move all PL/SQL variables into the USING clause. Fun.