[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

PL/SQL Procedure error

Posted on 2013-06-12
5
Medium Priority
?
409 Views
Last Modified: 2013-06-12
Hello experts,

I'd like to ask you for help with the following:

  procedure DEL_SHIPMENTS_CUSTOMER(cust_id_in number) is
    cnt NUMBER;
  begin
    cnt := 0;
    for rec in (
      select sc.shipment_id 
        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
    ) loop
      delete from SHIPMENT sh where sh.shipment_id = rec.shipment_id;
      cnt := cnt + 1;
      if (cnt = 1000) THEN
         commit;
         cnt := 0;
      end if;
    end loop;
    commit;
  end;
  
  procedure DEL_CUSTOMER(cust_id_in number) is
  begin
    UPDATE CUSTOMER cu SET MARK_FOR_DELETION = 'P' WHERE cu.customer_id = cust_id_in;
    commit;

    DEL_SHIPMENTS_CUSTOMER(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;
    commit;
  end;

begin
  -- Initialization
  null;
end MAINTAIN_CUSTOMER; 

Open in new window


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

Open in new window


Details are visible on the screenshot from toad.
Package is also enclosed.

Next

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!
error.bmp
0
Comment
Question by:Petju_22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39240358
As the error says, you are probably trying to delete the data in the parent tables for which child table has records. This is a referential integrity constraint violation.

You need to debug the code to understand which is the culprit record. Not all records may be causing the issue but you need to note that even if we have 1 child record for which you are trying to delete the parent table, then you will end up in this error.

Alternatively, ensure to delete/clean up  the child table records first before cleaning up the parent table records.

Thanks,
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 2000 total points
ID: 39240376
I belive that this is the offending line
DELETE FROM CUSTOMER WHERE CUSTOMER_ID = cust_id_in;

You have a table with the foreign key

C_REPORT_LINKS_REPORT_ID_FK - I guess a table possibly named C_REPORT_LINKS or similar that references the CUSTOMER Table. It will have a field with Customer_IDs and one or more IDs are being deleted by you. I suspect that you need to add thisa table into the delete statements or you need to review why you are deleting this data.

Kelvin
0
 

Author Comment

by:Petju_22
ID: 39240380
Could you please tell me how to enable tracing? I'm out of oracle business for a long long time and now I have to resolve this issue.
I'm directly in DB box running sqplus.
The calling of the mentioned package:

declare
begin
   for rec in (select * from (select cu.customer_id from customer cu where cu.mark_for_deletion = 'P') where rownum <=1)
    loop
      MAINTAIN_CUSTOMER.DEL_CUSTOMER(rec.customer_id);
   end loop;
end;

Open in new window


Now how to enable the tracing to be able to download and read the file (oracle trace reader e.g.) after the dead end of procedure?
0
 

Author Comment

by:Petju_22
ID: 39240742
Dear Kelvin,

I've found that there is a table called CUSTOM_REPORT_LINKS with Foreign Key Constraint called C_REPORT_LINKS_REPORT_ID_FK - so you are right, thank you!
I will have to alter the package now because data in this table must be deleted as well.

Time for candies ;)
0
 

Author Closing Comment

by:Petju_22
ID: 39240744
Perfect hint!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question