Raising Exceptions Explicitly

To raise an exception explicitly, use either the RAISE statement or RAISE_APPLICATION_ERROR procedure.

Topics

RAISE Statement

The RAISE statement explicitly raises an exception. Outside an exception handler, you must specify the exception name. Inside an exception handler, if you omit the exception name, the RAISE statement reraises the current exception.

Topics

Raising User-Defined Exception with RAISE Statement

In Example 12-10, the procedure declares an exception named past_due, raises it explicitly with the RAISE statement, and handles it with an exception handler.

Example 12-10 Declaring, Raising, and Handling User-Defined Exception

CREATE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
  past_due  EXCEPTION;  -- declare exception
BEGIN
  IF due_date < today THEN
    RAISE past_due;  -- explicitly raise exception
  END IF;
EXCEPTION
  WHEN past_due THEN  -- handle exception
    DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/
 
BEGIN
  account_status (TO_DATE('01-JUL-2010', 'DD-MON-YYYY'),
                  TO_DATE('09-JUL-2010', 'DD-MON-YYYY'));
END;
/

Result:

Account past due.

Raising Internally Defined Exception with RAISE Statement

Although the runtime system raises internally defined exceptions implicitly, you can raise them explicitly with the RAISE statement if they have names. Table 12-3 lists the internally defined exceptions that have predefined names. "Internally Defined Exceptions" explains how to give user-declared names to internally defined exceptions.

An exception handler for a named internally defined exception handles that exception whether it is raised implicitly or explicitly.

In Example 12-11, the procedure raises the predefined exception INVALID_NUMBER either explicitly or implicitly, and the INVALID_NUMBER exception handler always handles it.

Example 12-11 Explicitly Raising Predefined Exception

DROP TABLE t;
CREATE TABLE t (c NUMBER);
 
CREATE PROCEDURE p (n NUMBER) AUTHID DEFINER IS
  default_number NUMBER := 0;
BEGIN
  IF n < 0 THEN
    RAISE INVALID_NUMBER;  -- raise explicitly
  ELSE
    INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999'));  -- raise implicitly
  END IF;
EXCEPTION
  WHEN INVALID_NUMBER THEN
    DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.');
    INSERT INTO t VALUES(default_number);
END;
/
 
BEGIN
  p(-1);
END;
/

Result:

Substituting default value for invalid number.
 
BEGIN
  p(1);
END;
/

Result:

Substituting default value for invalid number.

Reraising Current Exception with RAISE Statement

In an exception handler, you can use the RAISE statement to"reraise" the exception being handled. Reraising the exception passes it to the enclosing block, which can handle it further. (If the enclosing block cannot handle the reraised exception, then the exception propagates—see "Exception Propagation".) When reraising the current exception, you need not specify an exception name.

In Example 12-12, the handling of the exception starts in the inner block and finishes in the outer block. The outer block declares the exception, so the exception name exists in both blocks, and each block has an exception handler specifically for that exception. The inner block raises the exception, and its exception handler does the initial handling and then reraises the exception, passing it to the outer block for further handling.

Example 12-12 Reraising Exception

DECLARE
  salary_too_high   EXCEPTION;
  current_salary    NUMBER := 20000;
  max_salary        NUMBER := 10000;
  erroneous_salary  NUMBER;
BEGIN

  BEGIN
    IF current_salary > max_salary THEN
      RAISE salary_too_high;   -- raise exception
    END IF;
  EXCEPTION
    WHEN salary_too_high THEN  -- start handling exception
      erroneous_salary := current_salary;
      DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary ||' is out of range.');
      DBMS_OUTPUT.PUT_LINE ('Maximum salary is ' || max_salary || '.');
      RAISE;  -- reraise current exception (exception name is optional)
  END;

EXCEPTION
  WHEN salary_too_high THEN    -- finish handling exception
    current_salary := max_salary;

    DBMS_OUTPUT.PUT_LINE (
      'Revising salary from ' || erroneous_salary ||
      ' to ' || current_salary || '.'
    );
END;
/

Result:

Salary 20000 is out of range.
Maximum salary is 10000.
Revising salary from 20000 to 10000.

RAISE_APPLICATION_ERROR Procedure

You can invoke the RAISE_APPLICATION_ERROR procedure (defined in the DBMS_STANDARD package) only from a stored subprogram or method. Typically, you invoke this procedure to raise a user-defined exception and return its error code and error message to the invoker.

The RAISE_APPLICATION_ERROR procedure is marked with SUPPRESSES_WARNING_6009 pragma.

For semantic information, see "SUPPRESSES_WARNING_6009 Pragma".

To invoke RAISE_APPLICATION_ERROR, use this syntax:

RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);

You must have assigned error_code to the user-defined exception with the EXCEPTION_INIT pragma. The syntax is:

PRAGMA EXCEPTION_INIT (exception_name, error_code)

The error_code is an integer in the range -20000..-20999 and the message is a character string of at most 2048 bytes.

For semantic information, see "EXCEPTION_INIT Pragma".

The message is a character string of at most 2048 bytes.

If you specify TRUE, PL/SQL puts error_code on top of the error stack. Otherwise, PL/SQL replaces the error stack with error_code.

In Example 12-13, an anonymous block declares an exception named past_due, assigns the error code -20000 to it, and invokes a stored procedure. The stored procedure invokes the RAISE_APPLICATION_ERROR procedure with the error code -20000 and a message, whereupon control returns to the anonymous block, which handles the exception. To retrieve the message associated with the exception, the exception handler in the anonymous block invokes the SQLERRM function, described in "Retrieving Error Code and Error Message".

Example 12-13 Raising User-Defined Exception with RAISE_APPLICATION_ERROR

CREATE OR REPLACE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
BEGIN
  IF due_date < today THEN                   -- explicitly raise exception
    RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
  END IF;
END;
/
 
DECLARE
  past_due  EXCEPTION;                       -- declare exception
  PRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception
BEGIN
  account_status (TO_DATE('01-JUL-2010', 'DD-MON-YYYY'),
                  TO_DATE('09-JUL-2010', 'DD-MON-YYYY'));   -- invoke procedure

EXCEPTION
  WHEN past_due THEN                         -- handle exception
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/

Result:

ORA-20000: Account past due.