A user can define exceptions that are specifically associated with the domain of the program and not defined by Oracle. Such an exception must be declared (to effectively create and initialize it as a type). Later it must be raised within the context of the program when the conditions occur that constitute an exception. It can be handled in the same manner as a system generated exception. A user defined error can also be raised using the Oracle supplied RAISE_APPLICATION_ERROR stored procedure.
DECLARE my_new_exception EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Starting...'); DBMS_OUTPUT.PUT_LINE('...Ending'); END;
You can declare your own exceptions for use later in a program. A declaration does not cause the exception to be raised, it simply declares it in a manner similar to any other variable type.
DECLARE my_new_exception EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Starting'); RAISE my_new_exception; END;
When you want to cause a user defined exception to occur in code, you use the RAISE keyword.
DECLARE my_new_exception EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Starting'); -- Normally we'd have an IF or CASE here RAISE my_new_exception; -- Normally we'd close the IF or CASE here DBMS_OUTPUT.PUT_LINE('This will not display.'); EXCEPTION WHEN my_new_exception THEN DBMS_OUTPUT.PUT_LINE('Our rule is broken!'); END;
A user defined exception that has been raised can be handled like any other exception. This example also includes a line of code below the raised exception that illustrates the control flow. When an exception is raised, normal processing ceases and the relevant exception handler in the current block is invoked.
BEGIN RAISE_APPLICATION_ERROR(-20000, 'A user defined error occurred.'); END;
The RAISE_APPLICATION_ERROR procedure allows you to raise user defined errors from stored subprograms that will display messages in the same format as Oracle error messages. In this case, an exception does not need to be previously declared. The error number specified must be a negative integer between -20000 and -20999.