O9i: MERGE tables across database links

From: <MxmsG9_at_netscape.net>
Date: Wed, 26 Nov 2003 20:14:24 -0800
Message-ID: <F001.005D7EBB.20031126201424@fatcity.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 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?

---

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: MxmsG9_at_netscape.net

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: ListGuru_at_fatcity.com (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).

Received on Wed Nov 26 2003 - 22:14:24 CST