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
Solved

PL/SQL Procedure error

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
make null the repeated levels 2 31
Find results from sql within a time span 11 45
T-SQL: New to using transactions 9 46
oracle forms question 22 39
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

792 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