EXCEPTION

EXCEPTION marks the exception handling section of a scripting block and defines how exceptions raised within the block should be handled.

See also BEGIN ... END and DECLARE as additional parts of a script block.

Syntax

[DECLARE]
  { variable | resultset | exception };
  [{ variable | resultset | exception }; ...]
[BEGIN]
  ... statements; 
EXCEPTION
    WHEN exception_name [ OR exception_name ... ] THEN
        statement;
        [ statement; ... ]
    [ WHEN ... ]
    [ WHEN OTHER THEN ]
        statement;
        [ statement; ... ]
[END;]

The DECLARE and EXCEPTION sections are optional within a block.

Arguments

exception_name: An exception name defined in the DECLARE part of the current or an enclosing block.

---

statement: One or more statements of the following types:

• A single SQL statement
• A control-flow statement (e.g. loop or conditional)
• A nested block
• A stored procedure call using CALL

Usage Notes

• Each block can have its own exception handler.
• Only one exception handler per block is supported. However, you can handle multiple exception types using a single handler with multiple WHEN clauses.
• Statements that follow the exception handler in the block will not be executed. Therefore, the handler must be placed at the end of the block.
• The WHEN OTHER THEN clause allows handling unspecified exceptions.
• If more than one WHEN clause matches a given exception, only the first matching clause is executed. Others are ignored.
• Only exceptions in scope can be handled by an exception handler.
• If a stored procedure is expected to return a value, it must return a value on every possible path, including each WHEN clause in the handler.

Example 1

The exception handler handles more than one type of exception.

DECLARE
  RES STRING;
  EXC_A EXCEPTION (-20001, 'EXC_A occurs.');
  EXC_B EXCEPTION (-20002, 'Exception is not in expected exceptions!');
BEGIN
  RES := 'No exceptions. ';
  IF (TRUE) THEN
    RAISE EXC_A;
  END IF;
  RETURN RES;
EXCEPTION
  WHEN EXC_B THEN
    SELECT 'catched B';
  WHEN EXC_A THEN
    SELECT 'catched A';
END;

+-----------------+
| result          |
|-----------------|
| catched A       |
+-----------------+

Example 2 | Nested Blocks

An inner block can raise an exception defined in either the inner or outer block. In this example, only the inner exception is caught by the inner block.

DECLARE
  EXC_A EXCEPTION (-20001, 'Exception EXC_A');
BEGIN
  -- Inner block.
  DECLARE
    EXC_B EXCEPTION (-20002, 'Exception EXC_B');
  BEGIN
    IF (TRUE) THEN
      RAISE EXC_A;
    ELSE
      RAISE EXC_B;
    END IF;
  EXCEPTION
    WHEN EXC_B THEN
      SELECT 'catched B';
  END;
EXCEPTION
  WHEN EXC_A THEN
    SELECT 'catched A';
  WHEN OTHER THEN
    SELECT 'catched other';
END;

+-----------------+
| result          |
|-----------------|
| catched A       |
+-----------------+

Example 3 | Multiple exceptions with OR and WHEN OTHER THEN

DECLARE
  EXC_A EXCEPTION (-20001, 'EXC_A occurs.');
  EXC_B EXCEPTION (-20002, 'EXC_B occurs.');
  EXC_C EXCEPTION (-20003, 'ESC_C occurs.');
BEGIN
  IF (TRUE) THEN
    RAISE EXC_A;
  END IF;
EXCEPTION
  WHEN EXC_A OR EXC_B THEN
    SELECT 'catched A or B';
  WHEN EXC_C THEN
    SELECT 'catched C';
END;

+-----------------+
| result          |
|-----------------|
| catched A or B  |
+-----------------+

Example 4 | Returning a value from every possible path

-----  NOT RUN  -----
DECLARE
  EXC_A EXCEPTION (-20001, 'EXC_A occurs.');
  EXC_B EXCEPTION (-20002, 'EXC_B occurs.');
BEGIN
  statement_1;
  ...
  RETURN a;
EXCEPTION
  WHEN EXC_A THEN
    BEGIN
      ...
      RETURN b;
    END;
  WHEN EXC_B THEN
    BEGIN
      ...
      RETURN c;
    END;
END;