How to catch constraint violation in PL/SQL?

Question

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.

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.

Comments

[Matthew Moisen]

Is there a way to identify which constraint was violated if the table has multiple check constraints?

[Bob Jarvis - Слава Україні]

I believe that the name of the constraint will be in SQLERRM, along with other text. You could either print SQLERRM as shown above, or parse the text to find the constraint name.