oracle-databaseplsqlsql-insertdbeaverora-00001

ORA-00001: uniqueness constraint violated


I have this procedure:

declare
  datafix_id$c varchar2(100) := 'BSS-39038';
  datafix_version$c varchar2(100) := '1.0';
  description$c varchar(100) := 'BSS-39038 [CBM] Добавить ERT_SERVICE_ID на продукты облачных сервисов';
  cnt$i integer;
begin
  select count(1)
  into cnt$i
  from ert_datafixes_log edl1
  where edl1.datafix_id = datafix_id$c
    and edl1.datafix_version = datafix_version$c
    and edl1.result = 'APPLIED'
    and not exists(select 1
                   from ert_datafixes_log edl2
                   where edl2.datafix_id = edl1.datafix_id
                     and edl2.datafix_version = edl1.datafix_version
                     and edl2.result = 'ROLLEDBACK'
                     and edl2.execution_date > edl1.execution_date
                  );
 
  if cnt$i = 0 then
    insert into ert_datafixes_log(datafix_id, datafix_version, execution_date, result)
    values(datafix_id$c, datafix_version$c, current_date, 'APPLIED');
   
    insert into TMP_BSS39038_PRODUCTATTRIBUTE1 
    select distinct *
    from PRODUCTATTRIBUTE@rbm_dblink p
    where p.PRODUCT_ID in (8608833, 8608674, 8608675, 8608676, 8608677, 8608678, 8608679, 8608680,8608681,8608682,8608818,8608819,8608820,8608821,8608822,8608823,8608824,8608825,8608826,8608827,8608828,8608829,8608830,8608831,8608832);
     
    insert into TMP_BSS39038_PRODUCTATTRIBUTE2 
    select *
    from PRODUCTATTRIBUTE@rbm_dblink p
    where p.PRODUCT_ID in (8608246, 8608248, 8608249, 8608250, 8608251, 8608252, 8608253, 8608254, 8608255, 8608256, 8608257, 8608258, 8608259, 8608260, 8608261, 8608262, 8608263, 8608264, 8608265, 8608266, 8608267, 8608268)
    and p.PRODUCT_ATTRIBUTE_SUBID = 6;
     
    for rec1 in (select product_id
                from TMP_BSS39038_PRODUCTATTRIBUTE1
               )
      loop
        insert into PRODUCTATTRIBUTE@rbm_dblink (PRODUCT_ID, PRODUCT_ATTRIBUTE_SUBID, ATTRIBUTE_UA_NAME, ATTRIBUTE_BILL_NAME, 
        ATTRIBUTE_CLASS, MANDATORY_BOO, BREAKOUT_OBJECT, BREAKOUT_GET_BUTTON_BOO, DISPLAY_POSITION, PROV_ATTR_NUM, ATTRIBUTE_UNITS)
        values (rec1.PRODUCT_ID, 6, 'ERT_SERVICE_ID', 'ERT_SERVICE_ID', 1, 'F', '', 'F', 6, '', 'TX');
       
      end loop;
      
    for rec2 in (select PRODUCT_ID, PRODUCT_ATTRIBUTE_SUBID
                from TMP_BSS39038_PRODUCTATTRIBUTE2
               )
      loop
        update PRODUCTATTRIBUTE@rbm_dblink 
        set DISPLAY_POSITION=6, ATTRIBUTE_UA_NAME='ERT_SERVICE_ID', ATTRIBUTE_BILL_NAME='ERT_SERVICE_ID'
        where PRODUCT_ID = rec2.product_id and PRODUCT_ATTRIBUTE_SUBID = rec2.PRODUCT_ATTRIBUTE_SUBID;
       
      end loop;
 
    commit;
    dbms_output.put_line('Датафикс применен успешно');
  else
    dbms_output.put_line('Датафикс уже был применен');
  end if;
end;

Running the procedure results in an error: SQL Error [2055] [42000]: ORA-02055: distributed update operation failed; rollback required ORA-00001: uniqueness constraint violated (GENEVA_ADMIN.PRODUCTACTRIBUTE_PK) ORA-02063: preceding line from RBM_DBLINK ORA-06512: on line 45 ORA-06512: on line 45

I understand that the "insert" operation leads to an error, but I don't understand why

If the input operation is done with a specific id, then the error does not occur. For example:

insert into PRODUCTATTRIBUTE@rbm_dblink (PRODUCT_ID, PRODUCT_ATTRIBUTE_SUBID, ATTRIBUTE_UA_NAME,
        ATTRIBUTE_BILL_NAME, ATTRIBUTE_CLASS, MANDATORY_BOO, BREAKOUT_OBJECT, BREAKOUT_GET_BUTTON_BOO, DISPLAY_POSITION, PROV_ATTR_NUM, ATTRIBUTE_UNITS)
        values (8608833, 6, 'ERT_SERVICE_ID', 'ERT_SERVICE_ID', 1, 'F', '', 'F', 6, '', 'TX')

This operation finished successfully.

PRODUCTATTRIBUTE_PK consists of PRODUCT_ID (NUMBER(9,0)) and PRODUCT_ATTRIBUTE_SUBID (NUMBER(9,0))


Solution

  • This part populates the temporary table with existing records from PRODUCTATTRIBUTE@rbm_dblink

       insert into TMP_BSS39038_PRODUCTATTRIBUTE1 
        select distinct *
        from PRODUCTATTRIBUTE@rbm_dblink p
        where p.PRODUCT_ID in (8608833, 8608674, 8608675, 8608676, 8608677, 8608678, 8608679, 8608680,8608681,8608682,8608818,8608819,8608820,8608821,8608822,8608823,8608824,8608825,8608826,8608827,8608828,8608829,8608830,8608831,8608832);
    

    And then the code loops through that temporary table and tries to insert the records in the same table they were selected from.

        for rec1 in (select product_id
                    from TMP_BSS39038_PRODUCTATTRIBUTE1
                   )
          loop
            insert into PRODUCTATTRIBUTE@rbm_dblink (PRODUCT_ID, PRODUCT_ATTRIBUTE_SUBID, ATTRIBUTE_UA_NAME, ATTRIBUTE_BILL_NAME, 
            ATTRIBUTE_CLASS, MANDATORY_BOO, BREAKOUT_OBJECT, BREAKOUT_GET_BUTTON_BOO, DISPLAY_POSITION, PROV_ATTR_NUM, ATTRIBUTE_UNITS)
            values (rec1.PRODUCT_ID, 6, 'ERT_SERVICE_ID', 'ERT_SERVICE_ID', 1, 'F', '', 'F', 6, '', 'TX');
           
          end loop;
    

    So ... for all of those primary key values that are in the INSERT statement, a row in the target table already exists and that raises the unique constraint violation.