I'd like to ask you for help with the following:
procedure DEL_SHIPMENTS_CUSTOMER(cust_id_in number) is
cnt := 0;
for rec in (
from SHIPMENT_CUSTOMER sc
inner join CUSTOMER_MOT mt
on mt.customer_mot_id = sc.customer_mot_id
where mt.customer_id = cust_id_in
delete from SHIPMENT sh where sh.shipment_id = rec.shipment_id;
cnt := cnt + 1;
if (cnt = 1000) THEN
cnt := 0;
procedure DEL_CUSTOMER(cust_id_in number) is
UPDATE CUSTOMER cu SET MARK_FOR_DELETION = 'P' WHERE cu.customer_id = cust_id_in;
DELETE FROM RULE WHERE CUSTOMER_ID = cust_id_in;
DELETE FROM MOT_RULE WHERE CUSTOMER_ID = cust_id_in;
DELETE FROM ACCOUNT WHERE CUSTOMER_ID = cust_id_in;
DELETE FROM CUSTOMER_STATION WHERE CUSTOMER_ID = cust_id_in;
DELETE FROM CUSTOMER WHERE CUSTOMER_ID = cust_id_in;
This procedure is ending with the following error:
Error at line 3
ORA-02292: integrity constraint (XM.C_REPORT_LINKS_REPORT_ID_FK) violated - child record found
ORA-06512: at "XM.MAINTAIN_CUSTOMER", line 34
ORA-06512: at line 5
Details are visible on the screenshot from toad.
Package is also enclosed.
Could you please tell me where is the problem and if it is possible to tune this package because it is terribly slow? For example it is deleting 10 records for two hours!
I will provide you with anything else you'll need.
Thanks a lot in advance!