[KEEPER] Update with CASE statement causing out of sync (4360523)

[KEEPER] Update with CASE statement causing out of sync

When updating source table with CASE statement in such a way that one of the unique key column values are swapped between two existing rows, target table goes out of sync with unique constraint violation.

On source, oracle does not raise unique constraint violation as it is one update that changes both rows.  On target, SharePlex post executes update statement for each row separately based on how it is written in redo log and it raises unique constraint violation.

unsupported operation

WORKAROUND:

Change the unique index to unique constraint deferrable or change the unique index to non-unique index on target.

ST108033

For example, Create table test (id number, id1 number, id2 number, name varchar2(10)); Alter table test add primary key (id); Create unique index on test (id1, id2); Insert into test values (1,1,1,’test’); Insert into test values (2,2,1,’test’); Commit; Update test set id1= case when id=1 then 2 when id=2 then 1 else id1 end where (id = 1) or (id = 2); Commit; On source there is no unique constraint violation as oracle suppresses it but on target we execute separate update for each row and will encounter unique constraint violation. update test set id1=2 where id=1; update test set id1=1 where id=2; commit;