Oracle/PLSQL: WHEN OTHERS Clause

What is a WHEN OTHERS clause?

The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by your Named System Exceptions and Named Programmer-Defined Exceptions.

The syntax for the WHEN OTHERS clause in a procedure is:

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

EXCEPTION     WHEN exception_name1 THEN         [statements]

    WHEN exception_name2 THEN         [statements]

    WHEN exception_name_n THEN         [statements]

    WHEN OTHERS THEN         [statements]

END [procedure_name];

The syntax for the WHEN OTHERS clause in a function is:

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

EXCEPTION     WHEN exception_name1 THEN         [statements]

    WHEN exception_name2 THEN         [statements]

    WHEN exception_name_n THEN         [statements]

    WHEN OTHERS THEN         [statements]

END [function_name];

Here is an example of a procedure that uses a WHEN OTHERS clause:

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 DUP_VAL_ON_INDEX THEN         raise_application_error (-20001,'You have tried to insert a duplicate order_id.');

    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, if an exception is encountered that is not a DUP_VAL_ON_INDEX or a no_sales, it will be trapped by the WHEN OTHERS clause.