Solved

PL/SQL Procedure error

Posted on 2013-06-12
5
403 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 500 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

688 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