Catching Errors in procedure

oracleplsql

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;

Best Answer

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.

Related Question