Hi Tom,
This is my first here. Really getting fuzzy about this.
This is my sql wherein i want to apply the updates on the table
ru_receipt_updates to the rows of
trans_hdr table located on another server.
Though the columns on the tables has different names, I took effort to qualify it with the target table alias.
FOR C_ROW IN (SELECT * FROM CONSO_RECEIPT_UPDATES ORDER BY RU_RECEIPT_UPDATED) LOOP
MERGE INTO HPACC.TRANS_HDR@CONSO_HPACC_LINK T
USING (SELECT * FROM CONSO_RECEIPT_UPDATES WHERE RU_ID=C_ROW.RU_ID ) S
ON (T.TH_RECEIPT_NO=S.RU_RECEIPT_NO AND T.TH_BRANCH=S.RU_BRANCH AND T.TH_TRX_NO=S.RU_TRX_NO)
WHEN MATCHED THEN
UPDATE SET
T.TH_PAY_MODE_CODE=S.RU_PAYMODE,
T.TH_PAY_MODE_DESC=S.RU_PAYMODE_DESC,
T.TH_CANCELLED_FLAG=S.RU_CANCELLED_FLAG,
T.TH_CANCELLED_REMARK=S.RU_CANCEL_REMARK,
T.TH_RECEIPT_UPDATED=S.RU_RECEIPT_UPDATED,
T.TH_RECEIPT_UPDATED_BY=S.RU_RECEIPT_UPDATED_BY
WHERE T.TH_RECEIPT_UPDATED < S.RU_RECEIPT_UPDATED
;
DELETE FROM CONSO_RECEIPT_UPDATES WHERE RU_ID=C_ROW.RU_ID;
END LOOP;
But keeps throwing the error
"RECEIPT UPDATE 6310 ORA-00904: "A2"."TH_RECEIPT_UPDATED": invalid identifier ORA-02063: preceding line from CONSO_HPACC_LINK"
After a series of painstaking approach I suppose I got lucky by finally removing the table aliased column.
MERGE INTO HPACC.TRANS_HDR@CONSO_HPACC_LINK T
USING (SELECT * FROM CONSO_RECEIPT_UPDATES WHERE RU_ID=C_ROW.RU_ID ) S
ON (T.TH_RECEIPT_NO=S.RU_RECEIPT_NO AND T.TH_BRANCH=S.RU_BRANCH AND T.TH_TRX_NO=S.RU_TRX_NO)
WHEN MATCHED THEN
UPDATE SET
TH_PAY_MODE_CODE=S.RU_PAYMODE,
TH_PAY_MODE_DESC=S.RU_PAYMODE_DESC,
TH_CANCELLED_FLAG=S.RU_CANCELLED_FLAG,
TH_CANCELLED_REMARK=S.RU_CANCEL_REMARK,
TH_RECEIPT_UPDATED=S.RU_RECEIPT_UPDATED,
TH_RECEIPT_UPDATED_BY=S.RU_RECEIPT_UPDATED_BY
WHERE TH_RECEIPT_UPDATED < S.RU_RECEIPT_UPDATED
;
I can barely understand why, heard from other forums something of the dynamic assignment of oracle to the column names during runtime but i think that doesn't apply when you definitely qualify your columns. Not sure though!
That looks like a bug to me. There's been a few bugs with MERGE across db links, most of which are fixed in later versions.
Naming across database links is differently to "normal", because the query is often transformed to handle the link. For example
merge into t@db11 t
using ( select * from t ) m
on ( t.object_id = m.object_id )
when matched
then update set t.owner = m.owner;
gives a plan of
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
----------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT REMOTE| | 1 | 211 | 5 (0)| 00:00:01 | | |
| 1 | MERGE | T | | | | | DB11 | |
| 2 | VIEW | | | | | | | |
|* 3 | HASH JOIN | | 1 | 1166 | 5 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS FULL | T | 2 | 438 | 3 (0)| 00:00:01 | DB11 | |
| 5 | VIEW | | 6 | 5682 | 2 (0)| 00:00:01 | | |
| 6 | REMOTE | T | 6 | 336 | 2 (0)| 00:00:01 | ! | R->S |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
6 - SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJ
ECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECO
NDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED" FROM
"T" "A5" (accessing '!' )
Notice the 'arrival' of aliases like "A1","A2","A3" etc.
In your case, I suspect the transformation got something wrong along the way. It would be worth logging with Support.