0 Replies Latest reply on May 24, 2014 4:09 PM by c022ed2b-ecdf-47ed-8a27-88d7f170ef50

    DML_HANDLER User Procedure Creation Help Needed

    c022ed2b-ecdf-47ed-8a27-88d7f170ef50

      Hi

       

      I am testing Oracle Streams Synchronous Capture and I am stuck with the DML_HANDLERS for conflict resolution.

       

      I have setup 3 sites (multi master) environment. UPDATE conflicts are gracefully handled by Oracle supplied packages.

       

      My issue is with INSERT conflicts especially where Unique or Primary Keys are involved. Below are sample tables

       

       

      CREATE TABLE MYTABLE1
      (COL1    NUMBER(10),
      COL2    NUMBER(5),
      TIME    TIMESTAMP WITH TIME ZONE);
      
      ALTER TABLE MYTABLE1
      ADD CONSTRAINT MYTABLE1_COL1_PK
      PRIMARY KEY(COL1);
      
      
      CREATE TABLE MYTABLE2
      (COL1    NUMBER(9),
      COL2    NUMBER(10),
      COL3    NUMBER(3),
      TIME    TIMESTAMP WITH TIME ZONE);
      
      ALTER TABLE MYTABLE2
      ADD CONSTRAINT MYTABLE2_COL1_COL2_PK
      PRIMARY KEY(COL1, COL2);
      
      ALTER TABLE MYTABLE2
      ADD CONSTRAINT MYTABLE2_COL2_UK
      UNIQUE(COL2);
      

       

       

      I searched the entire forums and although there are some very good suggestions like converting INSERTS into UPDATES, I cannot really get it to work.

      I am not very good with stored procedures and hence having trouble creating one for the above scenario.

       

      I would really appreciate if someone could help me with it.

       

      Thank you