Qlik Replicate and Oracle source: Invisible primary key – not recognized by replicate task

[Michael_Litz]

An Oracle table has a primary key that is defined as Invisible which the Qlik Replicate task does not use. Qlik Replicate is encountering errors when updating rows in a table from Oracle invisible primary keys (PKs).

Message in the task log file:

[TASK_MANAGER ]W: Table 'XYZ'.D_XYZ' (subtask 0 thread 1) is suspended. Failed to build 'where' statement; Failed to get update statement for table XYZ'.D_XYZ', stream position 00007776: 2024-03-22T06:52:46:484188 [ASSERTION ]V: 'UPDATE (3)' event of table XYZ'.D_XYZ' with id '2065373' does not contain all key values (0 from 1), stream position '00000792.a5e2a494.00000001.0008.01.0000:44682.246184.16' (streamcomponent.c:2984)

The primary key is correctly defined in both source and target Databases and the insert statements are correctly replicated.

Excerpt from table DDL:

CREATE TABLE XYZ.D_XYZ
(
MLOT                VARCHAR2(4 BYTE)        NOT NULL,
MLDAY                NUMBER(6)               NOT NULL,
NUM_SEQ  NUMBER INVISIBLE        NOT NULL,

SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS

ALTER TABLE XYZ.D_XYZ ADD (
CONSTRAINT NUM_SEQ_PK
PRIMARY KEY
(NUM_SEQ)
USING INDEX XYZ.NUM_SEQ_PK
ENABLE VALIDATE);

We use the advanced setting 'Support invisible' columns.

The task does not use the key even when the Configuration Parameter 'Support invisible columns' is set.

Resolution

The table behaves as if there were no primary key at all and the task tries to build a where clause with all fields.

In this case that did not work because the source table only had Primary Key logging enabled.

To get this to work we enables ALL COLUMN supplemental logging on the source table and the task was able to build the correct where clause for updates.

Related Content

Oracle Advanced connections Tab