In this case, we need access to the error message itself to gain knowledge about what went wrong. The WHEN OTHERS clause must be used so the SQLCODE and SQLERRM can be captured and evaluated. These two PL/SQL functions, which mimic their PRO*Language analogs, are only available within an exception handler, and are most useful in a WHEN OTHERS clause. In addition, the function called strip_constraint_name will accept the text of an Oracle error and return in capital letters the name of the constraint that was violated. Consider two examples:
Example 1
/*
** Example of using SQLCODE/SQLERRM in WHEN OTHERS
*/
DECLARE
lv_sqlcode NUMBER; /* Place to hold SQLCODE */
lv_sqlerrm VARCHAR2(240); /* Place to hold SQLERRM */
lv_constr VARCHAR2(41); /* Place for Constraint Name */
BEGIN
UPDATE PARENT_TABLE
SET SOME_FIELD = 5
WHERE PRIMARY_KEY_FIELD = :BLOCK.PK;
/*
** If we get here, then things went ok.
*/
EXCEPTION
/*
** If an error arises, the exception handler gets control
*/
WHEN OTHERS THEN
lv_sqlcode := SQLCODE;
lv_sqlerrm := SQLERRM;
IF (lv_sqlcode = -2290) THEN
/*
** Strip out the name of the violated constraint
*/
lv_constr := strip_constraint_name(lv_sqlerrm);
IF (lv_constr = 'SCOTT.C_CK') THEN
Message('Code must be A,B, or C');
RAISE Form_Trigger_Failure;
END IF;
END IF;
END;
Example 2
/* STRIP_CONSTRAINT_NAME: Returns constraint name from
error
** Constraint name should appear enclosed by parentheses
** in the Oracle errors 02290-02292 and 02296-02299.
** Return the text between the parentheses when passed
** the error message text.
*/
FUNCTION strip_constraint_name( errmsg VARCHAR2 )
RETURN VARCHAR2
IS
lv_pos1 NUMBER;
lv_pos2 NUMBER;
BEGIN
lv_pos1 := INSTR(errmsg, '(');
lv_pos2 := INSTR(errmsg, ')');
IF (lv_pos1 = 0 OR lv_pos2 = 0 ) THEN
RETURN(NULL);
ELSE
RETURN(UPPER(SUBSTR(errmsg, lv_pos1+1,
lv_pos2-lv_pos1-1)));
END IF;
END;
To trap ORACLE errors that are a result of database block INSERT, UPDATE, and DELETE operations, you must code the respective ON-INSERT, ON-UPDATE, and/or ON-DELETE triggers to actually perform the DML operations so that you can trap the errors.
Errors related to the features discussed above that could be trapped by the first example are: