Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to update remote database using another remote database in merge statement

Nabila islamOct 8 2018 — edited Oct 10 2018

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

Comments
Post Details
Added on Oct 8 2018
15 comments
775 views