Hi Chris/Connor,
TYPE tab_Varchar2 IS TABLE OF VARCHAR2(500);
Before executing MERGE we are fetching ROWIDs of TB_ORDERS in chunk using BULK COLLECT LIMIT 5000;
DBLINK is created which is poitning to Archival DB, and we need to MERGE records from Primary to Archival DB.
Here is the MERGE command, its thorwing error - ORA-22804: remote operations not permitted on object tables or user-defined type columns
Can you please help in this? any suggestion, alternative?
Thanks!!
MERGE /*+ PARALLEL(TB_ORDERS, 1) */ INTO TB_ORDERS@connect_tsys tgt
USING ( SELECT /*+ PARALLEL(TB_ORDERS, 1) */ * FROM TB_ORDERS WHERE ROWID IN (select column_value as name from table(cast(tb_Order_Rowids as tab_Varchar2)))) src
ON (src.CARD_NO = tgt.CARD_NO AND src.STMT_BEGIN_DATE = tgt.STMT_BEGIN_DATE)
WHEN MATCHED THEN
UPDATE
SET tgt.ACC_NO = src.ACC_NO, tgt.ACC_STATUS = src.ACC_STATUS
WHEN NOT MATCHED THEN
INSERT (tgt.CARD_NO, tgt.STMT_BEGIN_DATE, tgt.ACC_NO, tgt.PRIMARY_CARD_NO)
VALUES (src.CARD_NO, src.STMT_BEGIN_DATE, src.ACC_NO, src.PRIMARY_CARD_NO);