APPLY PARAMETER COMPARE_KEY_ONLY DOES NOT WORK AS EXPECTED WHEN TABLE HAS CLOB

Last updated on JULY 10, 2017

Applies to:

Symptoms

APPLY PARAMETER COMPARE_KEY_ONLY DOES NOT WORK AS EXPECTED WHEN TABLE HAS CLOB.

ORA-1403 are still recorded in DBA_APPLY_ERRORS;

PK with CLOB:
===========

SQL> create table USR1.tab_PK_CLOB (ID number primary key, Name varchar2(30)
,Name_desc  clob);

Table created.

SQL> insert into USR1.tab_PK_CLOB values (1,'krishna','oracle');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from USR1.tab_PK_CLOB
 2  ;

       ID NAME                           NAME_DESC
---------- ------------------------------
------------------------------------------------------------------------------
--
        1 krishna                        oracle

SQL> update  USR1.tab_PK_CLOB@strm112b set NAME='kris';

1 row updated.

SQL> commit;

Commit complete.

SQL> select ID , NAME from USR1.tab_PK_CLOB@strm112b;

       ID NAME
---------- ------------------------------
        1 kris

SQL> select * from USR1.tab_PK_CLOB
 2  ;

       ID NAME                           NAME_DESC
---------- ------------------------------
------------------------------------------------------------------------------
--
        1 krishna                        oracle

SQL> update USR1.tab_PK_CLOB set NAME='KRISHNA123';

1 row updated.

SQL> commit;

Commit complete.

SQL> select ID , NAME from USR1.tab_PK_CLOB@strm112b;

       ID NAME
---------- ------------------------------
        1 kris

DBA_APPLY_ERROR:
================
ORA-26786: A row with key ("ID") = (1) exists but has conflicting column(s)
"NAME" in table USR1.TAB_PK_CLOB
ORA-01403: no data found


NOTE: If same is performed for the same table without CLOB it works. This
happens only if the table contains CLOB.

Changes

 NONE.

Cause