Scope of an Exception

The scope of an exception is that portion of the code that is "covered" by that exception. An exception covers a block of code if it can be raised in that block. The following table shows the scope for each of the different kinds of exceptions:

Exception type	Description of scope
Named system exceptions	These exceptions are globally available because they are not declared in or confined to any particular block of code. You can raise and handle a named system exception in any block.
Named programmer-defined exceptions	These exceptions can be raised and handled only in the execution and exception sections of the block in which they are declared (and all nested blocks). If the exception is defined in a package specification, its scope is every program whose owner has EXECUTE authority on that package.
Anonymous system exceptions	These exceptions can be handled in any PL/SQL exception section via the WHEN OTHERS section. If they are assigned a name, then the scope of that name is the same as that of the named programmer-defined exception.
Anonymous programmer-defined exceptions	These exceptions are defined only in the call to RAISE_APPLICATION_ERROR, and then are passed back to the calling program.

Consider the following example of the exception overdue_balance declared in the procedure check_account. The scope of that exception is the check_account procedure, and nothing else:

PROCEDURE check_account (company_id_in IN NUMBER) IS overdue_balance EXCEPTION;BEGIN ... executable statements ... LOOP ... IF ... THEN RAISE overdue_balance; END IF; END LOOP;EXCEPTION WHEN overdue_balance THEN ...END;

I can RAISE the overdue_balance inside the check_account procedure, but I cannot raise that exception from a program that calls check_account. The following anonymous block will generate a compile error, as shown below:

DECLARE company_id NUMBER := 100;BEGIN check_account (100);EXCEPTION WHEN overdue_balance /* PL/SQL cannot resolve this reference. */ THEN ... END; PLS-00201: identifier "OVERDUE_BALANCE" must be declared

The check_account procedure is a "black box" as far as the anonymous block is concerned. Any identifiers—including exceptions—declared inside check_account are invisible outside of that program.

6.3 Raising Exceptions

There are three ways that an exception may be raised in your application:

· Oracle might raise the exception when it detects an error.

· You might raise an exception with the RAISE statement.

· You might raise an exception with the RAISE_APPLICATION_ERROR built-in procedure.

We've already looked at how Oracle raises exceptions. Now let's examine the different mechanisms you can use to raise exceptions.