Error handling in Pl/SQL I'm coding something in PL/SQL for the first time and have come up with a situation.

We are building a package to handle insertion tasks but it's supposed to handle the check violations instead of pre-checking the inserted data.

The thing is, check violations in Oracle return the same error code (-02290) and I want to display a relevant message regarding each different violation.

I tried parsing the SQLERRM with an IF THEN ELSEIF structure, but it doesn't seem to catch the other messages. Example:

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
     RAISE_APPLICATION_ERROR(-20001, 'Data already in table. Please check again.');
  WHEN OTHERS THEN
  BEGIN
  IF (SQLCODE = -02290) THEN
    IF (SQLERRM LIKE '%SIZE%')
    THEN
      RAISE_APPLICATION_ERROR(-20002, 'Size too large for product');
    ELSE
      RAISE_APPLICATION_ERROR(-20003, 'Other dimensions are not coherent');
    END IF;
  END IF;
  END;


Second message never gets shown. Is there a mistake in my checking?
You can define your own exceptions. If you have a package for defining business logic, you can define some exceptions in the package header then raise the proper exception when needed. Which means that you need to keep the exception numbers unique so that you can handle the exceptions from the calling procedures.

ssociating a PL/SQL Exception with a Number (EXCEPTION_INIT Pragma)