0

I have created procedure for orders table. The procedure checks if the order has shipped, then the customer can returns all/part of the shipped items. Once verified by the procedure and the order has shipped, order details will be replicated in deleted orders table. However, I get an error on the insert whenever I run the program. This is due to duplication - An UPDATE or INSERT statement attempted to insert a duplicate key. Is there anyway I can insert records for the returned item without showing an error? My procedure is:

CREATE PROCEDURE RET_ORD(PARAM_ONO ORDERS.ONO%TYPE)
  IS
    CURSOR CUR_RET
  IS
  SELECT * FROM ORDERS 
  WHERE ONO = PARAM_ONO;
  V_CUR CUR_RET%ROWTYPE;
BEGIN
  OPEN CUR_RET;
 LOOP
  FETCH CUR_RET INTO V_CUR;
  EXIT WHEN CUR_RET%NOTFOUND;
 IF V_CUR.SHIPPED IS NOT NULL THEN
  INSERT INTO          
  DELETED_ORDERS(ONO,CNO,ENO,ENAME,ORD_DE_D,STATUS)
  VALUES(V_CUR.ONO,V_CUR.CNO,V_CUR.ENO,USER,,SYSDATE,'RETURNED');  
  DELETE FROM ODETAILS
  WHERE ONO = P_ONO;
 ELSIF V_CUR.SHIPPED IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('CANNOT PROCESS ORDER '|| V_CUR.ONO);
  END IF;
 END LOOP;
 CLOSE CUR_RET;
END;
2
  • It's not very clear what you are looking for. If insert causes unique constraint/index violation , but you still want to insert then you have to drop the constraint / replace unique index with non-unique. If you want to catch error, it's done by EXCEPTION WHEN ... block (assuming plsql tag in the question is correct). If you want to keep constraint but avoid duplicates, rewrite the query so it doesn't insert duplicates. However, you cannot have both - unique constraint, and insert that violates the constraint.
    – a1ex07
    Dec 2, 2015 at 16:01

1 Answer 1

0

I'm guessing you're getting the unique constraint violation on the deleted_orders table because you are running this multiple times for the same ONO. To prevent subsequent calls using the same ONO from getting a unique constraint violation, you can either modify the select query to not find them like this:

SELECT * FROM ORDERS 
  WHERE ONO = PARAM_ONO
  AND ONO NOT IN (SELECT ONO FROM DELETED_ORDERS);

If instead you want feedback when this occurs rather than just ignoring the duplicate, you can nest the INSERT into a BEGIN...EXCEPTION...END block that catches the constraint violation and handles it by doing a DBMS_OUTPUT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.