User Defined Exceptions

Contact Us or call 1-877-932-8228
User Defined Exceptions

User Defined Exceptions

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.

Code Sample:

Exception-Clause/Demos/user_defined_exception_declaration.sql
DECLARE 

  my_new_exception EXCEPTION; 
  
BEGIN

  DBMS_OUTPUT.PUT_LINE('Starting...');
  
  DBMS_OUTPUT.PUT_LINE('...Ending');

END;

Code Explanation

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.

Code Sample:

Exception-Clause/Demos/user_defined_exception_raise.sql
DECLARE 
  my_new_exception EXCEPTION; 
BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting');
  RAISE my_new_exception;
END;

Code Explanation

When you want to cause a user defined exception to occur in code, you use the RAISE keyword.

Code Sample:

Exception-Clause/Demos/user_defined_exception_handle.sql
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;

Code Explanation

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.

Code Sample:

Exception-Clause/Demos/user_defined_raise_application_error.sql
BEGIN
  RAISE_APPLICATION_ERROR(-20000,
                          'A user defined error occurred.');
END;

Code Explanation

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.

Next