Oracle CDC by Attunity (SQL 2012) does not handle updated primary keys correctly by stefang_1234

Active

Type:	Bug
ID:	774367
Opened:	12/14/2012 8:00:32 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When a table has CDC enabled and the primary key is updated this should be treated as a delete followed by an insert. This is the behavior you get with SQL Server CDC, but Oracle CDC registers this as a normal update.

This problem makes it impossible to use cdc.fn_cdc_get_net_changes_<capture_instance> and MERGE to implement a simple replication.

Details (expand)

English

SQL Server 2012 - Developer Edition

SQL Engine

Windows 7 Enterprise (SP1)

US English

1) Setup an Oracle CDC replication instance to the standard table SCOTT.EMP
2) Change the primary key of one of the existing rows in this table
UPDATE SCOTT.EMP SET EMPNO=1 WHERE EMPNO=7499
3) Look in the SCOTT_EMP_CT table
SELECT * FROM SCOTT_EMP_CT
4) Look at the result from net_changes
select * from [cdc].[fn_cdc_get_net_changes_SCOTT_EMP](sys.fn_cdc_get_min_lsn('SCOTT_EMP'), sys.fn_cdc_get_max_lsn(), 'all with merge')

SELECT * FROM SCOTT_EMP_CT

returns

__$operation EMPNO
3 7499
4 1

(update before followed by update after)

select * from [cdc].[fn_cdc_get_net_changes_SCOTT_EMP](sys.fn_cdc_get_min_lsn('SCOTT_EMP'), sys.fn_cdc_get_max_lsn(), 'all with merge')

returns

__$operation EMPNO
5 1

(One row with the new key only)

SELECT * FROM SCOTT_EMP_CT

returns

__$operation EMPNO
1 7499
2 1

(delete followed by insert)

select * from [cdc].[fn_cdc_get_net_changes_SCOTT_EMP](sys.fn_cdc_get_min_lsn('SCOTT_EMP'), sys.fn_cdc_get_max_lsn(), 'all with merge')

returns

__$operation EMPNO
1 7499
5 1

This is exactly how CDC for SQL Server 2012 works. I expect that Oracle CDC should work in exactly the same way

X64

0 attachments

Posted by Faiz VP on 1/21/2013 at 2:36 AM

I haven't started implementing it yet but can anyone else verify that this bug exists?

Microsoft