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 [EMAIL PROTECTED]) 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 [EMAIL PROTECTED] 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 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 [EMAIL PROTECTED] 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? __________________________________________________________________ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
