Using WHEN OTHERS Clause in Oracle

This Oracle tutorial explains how to use the Oracle WHEN OTHERS clause with syntax and examples.

What is the WHEN OTHERS clause in Oracle?

The WHEN OTHERS clause is used to entice all final exceptions that have not been handled via your Named System Exceptions and Named Programmer-Defined Exceptions.

Syntax

We will take a seem to be at the syntax for the WHEN OTHERS clause in both strategies and functions.

Syntax for Procedures

The syntax for the WHEN OTHERS clause in a technique 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];

Syntax for Functions

The syntax for the WHEN OTHERS clause in a feature 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];

Example

Here is an instance of a system 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 way of the WHEN OTHERS clause.

Frequently Asked Questions

Question: Is there any way to get the ORA error wide variety (and/or description) for the mistakes that will fall into OTHERS?

Something like:

WHEN OTHERS THEN
   'Error number ' & Err.Number & ' has happened.'

Answer: Yes, you can use SQLCODE characteristic to retrieve the error number and SQLERRM function to retrieve the error message.

For example, you should elevate the error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END;

Or you could log the error to a table as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := SUBSTR(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;