普通のユーザ定義例外は、宣言したブロック内でしか有効ではない。

DECLARE
    HOGE EXCEPTION;
BEGIN
    DECLARE
        HOGE EXCEPTION;
    BEGIN
        RAISE HOGE;
    END;
EXCEPTION
    WHEN HOGE THEN
        DBMS_OUTPUT.PUT_LINE('HOGE');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('OTHERS');
END;
/

実行結果

OTHERS

同じ HOGE という名前で宣言している例外でも、ブロックが別だとその他の例外扱いになる。

内側のブロックで RAISE したユーザ定義例外を、外側のブロックでハンドリングしたい場合は、 PRAGMA EXCEPTION_INIT() を使う。

DECLARE
    FUGA EXCEPTION;
    PRAGMA EXCEPTION_INIT(FUGA, -2000);
BEGIN
    DECLARE
        HOGE EXCEPTION;
        PRAGMA EXCEPTION_INIT(HOGE, -2000);
    BEGIN
        RAISE HOGE;
    END;
EXCEPTION
    WHEN FUGA THEN
        DBMS_OUTPUT.PUT_LINE('FUGA');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('OTHERS');
END;
/

実行結果

FUGA

エラーコード（-2000）が同じになるようにユーザ定義例外を宣言すれば、外側の EXCEPTION ブロックで、内側で RAISE された例外をハンドリングできる。

RAISE_APPLICATION_ERROR を使う

と思ったら、 PRAGMA EXCEPTION_INIT() はエラーコードに数値リテラルしか受け付けない　＝　エラーコードを定数で宣言して一か所で管理できない、という残念仕様だったので、代わりに RAISE_APPLICATION_ERROR() を使う。

DECLARE
    ERROR_CODE NUMBER(5) := -20000;
BEGIN
    BEGIN
        RAISE_APPLICATION_ERROR(ERROR_CODE, 'MY EXCEPTION!!');
    END;
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = ERROR_CODE THEN
            DBMS_OUTPUT.PUT_LINE('[HOGE] SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM);
        ELSE
            DBMS_OUTPUT.PUT_LINE('[OTHERS] SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM);
        END IF;
END;
/

実行結果

[HOGE] SQLCODE=-20000, SQLERRM=ORA-20000: MY EXCEPTION!!

これなら定数で管理できるし、エラー時のメッセージも外側に伝えられる。

エラーコードに指定できるのは -20000 ～ -20999 までの 1000 個。

参考