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@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@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@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.!!