Following instructions of support note Streams Heterogeneous Apply To SQL*Server (Doc ID 313766.1) on setting up replication of a table from Oracle (using 11.2.0.4) to SQL-Server (using 2014 sp1).
Ran into error:
ORA-26711: remote table does not contain a primary key constraint
ORA-26712: remote object is "dbo"."TKHRDT3"@"HOLINK2"
The table in SQL-Server does have a primary key constraint. Created as per support note instructions - with sysconstraints on SQL-Server confirming this.
It seems that the replication (via the Gateway) runs the following remote SQL, and then chokes as no data is returned:
SELECT
RTRIM(A4."name"),RTRIM(A2."name"),'C',RTRIM(A1."name"),
' ',' ',' ',' ',' ',' ',' ',A2."crdate"
FROM "dbo"."sysusers"@holink2 A4,
"dbo"."sysconstraints"@holink2 A3,
"dbo"."sysobjects"@holink2 A2,
"dbo"."sysobjects"@holink2 A1
WHERE A3."constid"=A2."id"
AND A2."type"='C'
AND A3."id"=A1."id"
AND A4."uid"=A2."uid"
Seems to me the bug is with expecting the type to be C - it is not. According to https://technet.microsoft.com/en-us/library/ms177596(v=sql.110).aspx type C is a check constraint, whereas type K in a unique or primary key constraint (SQL-Server's data dictionary query confirmed this for the table and PK in question).
The following posting from last year, Oracle to MS SQL Server Data Replication using Oracle Streams 11gR2, seems to have run into this exact same error. OP said resolution was that a patch was applied to fix this.
Support note OERR: ORA-26711 "remote table does not contain a primary key constraint" Reference Note (Doc ID 288318.1) however states 'There are currently no documented database bugs linked to "ORA-26711" .'
Will appreciate any pointers on how to resolve this issue. Thanks.