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))
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.