1

On running two DELETE statements inside my own Package.Procedure:

DELETE FROM ENT_PLANT_RELATIVE WHERE CHILD_ID = plant_id;
DELETE FROM ENT_PLANT_ITEM WHERE PLANT_ID = plant_id;

... I receive an integrity constraint violation on the second DELETE (first works correctly):

SQL> call P.DeletePlantItem(112808,1,:err);
ORA-02292: integrity constraint
(XXXXX.EAITA_PLANT_ITEM_ID_FK) violated - child record found

There are no child records containing the FK in the table containing the constraint of that name:

SQL> select count(plant_item_id) from ent_application_item_to_access 
where plant_item_id = 112808;

COUNT(PLANT_ITEM_ID)
--------------------
                   0

... and there is only one column in that table using PLANT_ITEM_ID.

On running the suspect DELETE statement directly (copied from the procedure) in SQL*Plus (from which I call the procedure), it works as expected.

I have:

  • disabled the first integrity constraint shown, only to have another similar constraint appear on another table, similarly not containing the FK
  • disabled the second similar integrity constraint shown...ditto
  • inserted a single record into the master table and the one child table I am trying to delete from, and tried again with identical results to previously existing records
  • manually inserted a single item into ENT_PLANT_ITEM, commented out the unnecessary DELETE in the procedure, and tried to call it: same results
  • tried a COMMIT immediately after the first (successful) deletion from the child table (ENT_PLANT_RELATIVE)
  • used put_line to obtain the SQLERRM contains shown above identifying the constraint
  • checked for the existence of triggers inserting records (unnecessarily, I feel, as no child data exists in the table showing the constraint issue)
  • used IF to logically break the procedure into two separate procedures handling the two DELETE statements separately: same results
  • checked privileges as much as I know how, although I assume that because I can DELETE from one context (SQLPlus), and create the Package and Procedure in the first place, I should be able to from the Procedure (call)
  • also assumed that SQLERRM would report a privilege error
  • read the on-line Oracle documentation on constraints
  • read as many Stack Overflow questions as I could find that refer to constraints and referential integrity

The package definition is:

CREATE OR REPLACE PACKAGE P AS 
    PROCEDURE InsertPlantItem(plant_desc IN VARCHAR2, parent_desc IN VARCHAR2, test IN NUMBER, err OUT VARCHAR2);
    PROCEDURE DeletePlantItem(plant_id IN NUMBER, test IN NUMBER, err OUT VARCHAR2);
END P;

In summary, why can I delete row x in table y from the SQL*Plus command line but not via a stored procedure I created because of a constraint in a table I don't have any keys in?

Paul Morris
  • 21
  • 6
  • Can you plz show package definition, just to see whether those parameters you are using are in the order. – Rams Apr 02 '17 at 06:09
  • I have put the package definition above. I strongly suspect the parameters to be correct because the first DELETE from the child table works; the second delete uses the same plant_id. Thanks for taking a look. – Paul Morris Apr 02 '17 at 06:39

1 Answers1

1

The problem was indeed in the second DELETE statement:

DELETE FROM ENT_PLANT_ITEM WHERE PLANT_ID = plant_id;

I had created an IN parameter called plant_id, passing in a 6-digit number not realising I was effectively asking the procedure to delete all items in the database where a constraint wasn't going to stop it. It's rather more obvious in all capitals: 

DELETE FROM ENT_PLANT_ITEM WHERE PLANT_ID = PLANT_ID;

A column name obviously has higher precedence than a parameter.

Paul Morris
  • 21
  • 6