totn Oracle / PLSQL

Oracle / PLSQL: WHEN OTHERS Clause

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 trap all remaining exceptions that have not been handled by your Named System Exceptions and Named Programmer-Defined Exceptions.

Syntax

We will take a look at the syntax for the WHEN OTHERS clause in both procedures and functions.

Syntax for Procedures

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];

Syntax for Functions

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];

Example

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.

Frequently Asked Questions

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

Something like:

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

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

For example, you could raise 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;
Share on: