Out of Sync occurs on batch operations for update or delete with where clause involving columns with null value.
Errorlog.sql shows sql statement with RETURNING ROWID INTO :RID which indicates this is batch operation.
For example, updates like below should not be batched together when preimage for previous row has NULL and not NULL value while the next row have NOT NULL and NOT NULL value.
For example,
update table xxx set col1=Y, col2=Y where col1 is null and col2=N and keycol=xxxx;
update table xxx set col1=Z, col2=Z where col1=N and col2=N and keycol=yyyy;------ > It generates the wrong sql on target with where clause col1 is NULL and col2=N and keycol=yyyy;
Should only batch operations when the where clause pattern matches when involving null.
update table xxx set col1=Y, col2=Y where col1 is null and col2=N and keycol=xxxx;
update table xxx set col1=Z, col2=Z where col1 is null and col2=N and keycol=yyyy;
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center