How to catch constraint violation in PL/SQL?

check-constraint, constraints, oracle, plsql, sql

Solution

The error which occurs when a check constraint is violated is ORA-02290. Although there is no "standard" definition for this it's easy enough to declare your own exception so you can catch the -2290 when it's thrown. Let's say we have a table created as follows:

CREATE TABLE SOME_TABLE (COL1 CHAR(1) CHECK(COL1 IN ('Y', 'N')));

and that we then run the following block:

DECLARE
  -- First, declare and initialize an appropriate exception

  CHECK_CONSTRAINT_VIOLATED EXCEPTION;
  PRAGMA EXCEPTION_INIT(CHECK_CONSTRAINT_VIOLATED, -2290);
BEGIN
  INSERT INTO SOME_TABLE(COL1) VALUES ('X');  -- will violate the check constraint
  RETURN;
EXCEPTION
  WHEN CHECK_CONSTRAINT_VIOLATED THEN  -- catch the ORA-02290 exception
    DBMS_OUTPUT.PUT_LINE('INSERT failed due to check constraint violation');
  WHEN OTHERS THEN                     -- catch all other exceptions
    DBMS_OUTPUT.PUT_LINE('Something else went wrong - ' || SQLCODE ||
                         ' : ' || SQLERRM);
END;

If you create the table as shown earlier and then run the block above you'll find that the line 'INSERT failed due to check constraint violation' will show up on DBMS_OUTPUT.

Share and enjoy.

Problem

``` CREATE TABLE LOCATION ( LOCID VARCHAR2(5) , MINQTY NUMBER , MAXQTY NUMBER , PRIMARY KEY (LOCID) , CONSTRAINT CHECK_LOCID_LENGTH CHECK (LENGTH(LOCID) = 5) , CONSTRAINT CHECK_MINQTY_RANGE CHECK (MINQTY BETWEEN 0 AND 999) , CONSTRAINT CHECK_MAXQTY_RANGE CHECK (MAXQTY BETWEEN 0 AND 999) , CONSTRAINT CHECK_MAXQTY_GREATER_MIXQTY CHECK (MAXQTY >= MINQTY) ); CREATE OR REPLACE PROCEDURE ADD_LOCATION_TO_DB(ploccode VARCHAR2, pminqty NUMBER, pmaxqty NUMBER) AS BEGIN INSERT INTO location(locid, minqty, maxqty) VALUES (ploccode, pminqty, pmaxqty); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN RAISE_APPLICATION_ERROR(-20081, 'Duplicate Location ID'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20086,sqlerrm); END; ``` I created the above table with constraints. Now I want to test these constraints in PL/SQL procedure by catching them in exceptions. But I'm confused how to do it.

Original source