How Exceptions Are Raised

By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number using an EXCEPTION_INIT. Though, other user-defined exceptions should be raised explicitly by the RAISE statements.

Using the RAISE Statement

The PL/SQL subprograms and blocks must raise an exception only when an error makes it undesirable or impossible to finish the processing. You can place the RAISE statements for a given exception anywhere within the scope of that exception. In the illustration below, you alert your PL/SQL block to a user-defined exception named out_of_stock:

DECLARE

out_of_stock EXCEPTION;

number_on_hand NUMBER(4);

BEGIN

...

IF number_on_hand < 1 THEN

RAISE out_of_stock;

END IF;

EXCEPTION

WHEN out_of_stock THEN

-- handle the error

END;

You can also raise a predefined exception explicitly. In that way, an exception handler written for the predefined exception can process other errors, as the illustration below shows:

DECLARE

acct_type INTEGER;

BEGIN

...

IF acct_type NOT IN (1, 2, 3) THEN

RAISE INVALID_NUMBER; -- raise predefined exception

END IF;

EXCEPTION

WHEN INVALID_NUMBER THEN

ROLLBACK;

...

END;