Oracle PL/SQL - Assigning a code to a user-defined exception


User-defined exceptions don't have associated codes.

Therefore SQLCODE will return NULL if a user-defined exception is raised.

You can associate user-defined exceptions with a specific code number, using a pragma exception_init statement.

procedure p_validateSalary(i_empNo_nr NUMBER, i_new_sal_nr NUMBER)
    v_current_sal NUMBER;
    v_error_nr NUMBER;

    e_increaseTooLarge exception;
    pragma exception_init(e_increaseTooLarge,-20999);
    when increase_too_much then
        v_error_nr := sqlcode;
        insert into t_LogError (error_tx)values(i_empNo_nr||':'||v_error_nr);

The EXCEPTION_INIT statement is placed in the declaration section of the block.

It is a good practice to always place the EXCEPTION_INIT right next to the exception declaration.

When assigning a code to a user-defined exception, choose a code between ?20999 and ?20000 only.

Codes in this range distinguish user-defined exceptions from predefined exceptions.

Related Topic