Solved

PL/SQL Procedure error

Posted on 2013-06-12
5
398 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
  • 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

786 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