MERGE over db-links

From: Newbie <learning_still_at_hotmail.com>
Date: 18 Nov 2003 06:02:08 -0800
Message-ID: <6093c29e.0311180602.74f73d3c_at_posting.google.com>

Hi,

I am trying to merge data from two tables (over two servers, using dblink). Approx 40,000 records in each. Server1(source) and Server2(dest)

On Server1, db-link "dblink1" points to Server2 On Server2, db-link "dblink1" points to Server1

Schema names are same on the two servers.

<<PULL Data>>

Now, when i am on destination server (Server2), then

MERGE INTO myemp D
USING (SELECT * FROM myemp_at_dblink1) S ON (D.emp_id = S.emp_id)
WHEN MATCHED THEN

        UPDATE SET 
                 FIRST_NAME = S.FIRST_NAME, 
                 MIDDLE_NAME = S.MIDDLE_NAME, 
                 LAST_NAME = S.LAST_NAME, 
                 LAST_UPDATION_DATE = sysdate

WHEN NOT MATCHED THEN INSERT (EMP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, LAST_UPDATION_DATE)
VALUES (S.EMP_ID, S.FIRST_NAME, S.MIDDLE_NAME, S.LAST_NAME, sysdate);

works fine...

but if i am on source server (Server1), then

<<PUSH data>>

MERGE INTO myemp_at_dblink1 D
USING (SELECT * FROM myemp) S
ON (D.emp_id = S.emp_id)
WHEN MATCHED THEN

        UPDATE SET 
                 FIRST_NAME = S.FIRST_NAME, 
                 MIDDLE_NAME = S.MIDDLE_NAME, 
                 LAST_NAME = S.LAST_NAME, 
                 LAST_UPDATION_DATE = sysdate

WHEN NOT MATCHED THEN INSERT (EMP_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, LAST_UPDATION_DATE)
VALUES (S.EMP_ID, S.FIRST_NAME, S.MIDDLE_NAME, S.LAST_NAME, sysdate);

produces following error -

The following error has occurred:

ORA-01008: not all variables bound
ORA-02063: preceding line from EMFG_DBLINK1

Is it that for MERGE to work, data is MERGED into "local" table and we cannot execute MERGE on a remote table through db-link?

I have to do loads of validation and pre-processing on my server1 and when all data is updated in myemp, then it is to be copied over to myemp_at_dblink1. Replication is not to be used, have to work within the boundaries assigned. Other waye round, i'll have to create a wrapper sql script using sql*plus "connect" to connect to server2 and then calling MERGE (PULLING data) from there instead of PUSHING the updated data from server1. Is there any other way out?

Please help.!! Received on Tue Nov 18 2003 - 15:02:08 CET