Oracle/PLSQL: Named Programmer-Defined Exceptions

What is a named programmer-defined exception?

Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't defined already by PL/SQL. These are called Named Programmer-Defined Exceptions.

The syntax for the Named Programmer-Defined Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]

    exception_name EXCEPTION;

BEGIN
    executable_section

    RAISE exception_name ;

EXCEPTION
    WHEN exception_name THEN
        [statements]

    WHEN OTHERS THEN
        [statements]

END [procedure_name];

The syntax for the Named Programmer-Defined Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
    RETURN return_datatype
IS | AS
    [declaration_section]

    exception_name EXCEPTION;

BEGIN
    executable_section

    RAISE exception_name ;

EXCEPTION
    WHEN exception_name THEN
        [statements]

    WHEN OTHERS THEN
        [statements]

END [function_name];

Here is an example of a procedure that uses a Named Programmer-Defined Exception:

CREATE OR REPLACE PROCEDURE add_new_order
    (order_id_in IN NUMBER, sales_in IN NUMBER)
IS
    no_sales EXCEPTION;

BEGIN
    IF sales_in = 0 THEN
        RAISE no_sales;

ELSE
    INSERT INTO orders (order_id, total_sales )
    VALUES ( order_id_in, sales_in );
END IF;

EXCEPTION
     WHEN no_sales THEN
        raise_application_error (-20001,'You must have sales in order to submit the order.');

    WHEN OTHERS THEN
        raise_application_error (-20002,'An error has occurred inserting an order.');

END;

In this example, we have declared a Named Programmer-Defined Exception called no_sales in our declaration statement with the following code:

no_sales EXCEPTION;

We've then raised the exception in the executable section of the code:

IF sales_in = 0 THEN
RAISE no_sales;

Now if the sales_in variable contains a zero, our code will jump directly to the Named Programmer-Defined Exception called no_sales.

Finally, we tell our procedure what to do when the no_sales exception is encountered by including code in the WHEN clause:

WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');

We are also using the WHEN OTHERS clause to trap all remaining exceptions:

WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');