Solved

PL/SQL Procedure error

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
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.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

756 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