0 Replies Latest reply: Dec 3, 2012 6:23 AM by albertone RSS

    Oracle 11.1.0.7.0 Issue with Streams when using NULL value in a key field

    albertone
      Hi,

      we have an issue in our replicated environment using Streams.

      We have a replicated table using the following index:

      UK_S01_TEXT_FORMAT_TBL01 SOGID
      UK_S01_TEXT_FORMAT_TBL01 INDEX_TEXT_FORMAT_ID
      UK_S01_TEXT_FORMAT_TBL01 INDEX_LANGUAGE_LABEL
      UK_S01_TEXT_FORMAT_TBL01 INDEX_TEXT_FORMAT_COUNTER
      UK_S01_TEXT_FORMAT_TBL01 INDEX_PROVIDER_ID
      UK_S01_TEXT_FORMAT_TBL01 INDEX_TARIFF_PLAN_COSP_ID



      with field INDEX_PROVIDER_ID allowing NULL values. Actually we insert empty strings in there, but as far as I understand Oracle will convert it sliently in a NULL value, which is fine to our application.

      When we apply a change on that table (either update or delete) we get errors at streams level and the transaction is not propagated.

      Local Transaction ID: 6.28.11170
      Source Commit SCN: 25397175
      Source Commit Time: Dec-03-2012 12:21:30
      Error in Message: 1
      Error Message: ORA-26787: The row with key ("INDEX_LANGUAGE_LABEL", "INDEX_PROVIDER_ID", "INDEX_TARIFF_PLAN_COSP_ID", "INDEX_TEXT_FORMAT_COUNTER", "INDEX_TEXT_FORMAT_ID", "SOGID") = (ITALIAN, , , 0, MIKE, 124) does not exist in table
      SMS.S01_TEXT_FORMAT_TBL
      ORA-01403: no data found

      message: 1
      TableName: S01_TEXT_FORMAT_TBL
      Operation Type: DELETE
      _____ Old Data _____
      SOID : 1010000008
      SOGID : 124
      INDEX_TEXT_FORMAT_ID : MIKE
      INDEX_LANGUAGE_LABEL : ITALIAN
      INDEX_TEXT_FORMAT_COUNTER : 0
      TEXT_FORMAT : Hai raggiunto i 100MB di traffico incluso nello scatto
      APPLICA_PERIOD_CRI_ID :
      INDEX_TARIFF_PLAN_COSP_ID :
      INDEX_PROVIDER_ID :

      If I force the set of primary keys to be only the NOT NULL fields, I can successfully apply the error, but since we may have them storing both NULL and NOT NULL values, this is not a solution.

      I'm wondering why the DB on one hand converts the empty string in a NULL while inserting, but it looks the Apply process onStandby is not able to do the same when looking for the record before applying the transaction.

      Thanks in advance!

      Cheers,

      Mike