2 Replies Latest reply: Dec 21, 2011 9:36 PM by user13650310 RSS

    Apply DELETE TXN ORA-01403

    user13650310
      Good morning,

      I'm posting here today to get some advice on handling ORA-01403 on DELETE statements. The issue is that the source row values do not match the destination row values, and ultimately the best scenario would be to sync up there rows between the two sites to avoid the errors all together. However, I would like to have a handler in place to address these errors. I deally I would like to process the DELETE transaction regardless row value differences.

      Just to help explain this is the scenarion:
      Site 1
      Table_A
      Col_PK vale: 1
      Col2 value: 'Test'

      Site 2
      Table _A
      Col_PK vale: 1
      Col2 value: 'Test 1'

      now this delete statement is issued on Site 1
      DELETE FROM TABLE_A WHERE col_pk=1;

      This statement will fail oin Site 2 since col2 values are different with an ORA-01403.

      What I want to happen on Site 2 is for the DELETE to be processed. I know I'll need to write a DML Error handler to address this, but I wanted to get your thoughts on what the best process or method is.

      Thank you for your help!
        • 1. Re: Apply DELETE TXN ORA-01403
          pmcelroy-Oracle
          In addition to writing a DML handler to resolve the error, there are a couple of other techniques you could use:

          1) Configure automatic conflict resolution for the table DELETE statements so that there is no conflict detection for old values on DELETE statements. You use the DBMS_APPLY_ADM.COMPARE_OLD_VALUES procedure to configure this. This is the most common way to deal with this type of request
          (http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_apply.htm#i1006610)

          exec dbms_apply_adm.compare_old_values(object_name=>'Table_A',column_list=>'*',operation=>'DELETE',compare=>FALSE);
          --- this should result in no conflict detection for any non-key columns for DELETE statements on Table_A


          2) In 11.2, there is a declarative transformation(KEEP_COLUMNS) that can be used to eliminate multiple columns at a time for LCRs. Declarative transformations are rule -based - so keep in mind that the rule must be specific to DELETE operations in order for this to work. Prior to 11.2, you could do multiple DELETE_COLUMN transformations on a rule to eliminate multiple columns. You can find the documentation for both of these transformations in the DBMS_STREAMS_ADM package( http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_streams_adm.htm#CDEJFACB)
          • 2. Re: Apply DELETE TXN ORA-01403
            user13650310
            Thank you for your response. I actually did set the compare_old_values to false for the table columns and it works great. I really appreciate the feedback.