Hi All
I am trying to run below merge statement.
merge into schema1.target_table @ DB_LINK1 tgt
using(select column1, column2
from schema2.source_table @ DB_LINK2)src
on (tgt.column1 = src.column1
and tgt.column2 = src.column2)
when matched then update
set tgt.column3 = 'Updated';
And getting error:
SQL Error: ORA-02084: database name is missing a component
ORA-02063: preceding line from DB_LINK1
02084. 00000 - "database name is missing a component"
*Cause: supplied database name cannot contain a leading '.',
trailing '.' or '@', or two '.' or '@' in a row.
After analysing I assume a reason where 2 different remote database tables can not be used as a target table and source table. When I use any table from DB_LINK1 as a source table, query works.
Is this a limitation in merge that I can't update on remote database using a remote database query? If that is the case, what could be the work around? I tried using Synonym, doesn't help. I rewritten the query with an Update statement instead of merge, same error persists.
Database version for DB_LINK1, DB_LINK2: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Database version where from merge query is getting executed: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Any advise/help/suggestions would be appreciated.
Thanks
Nabila