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)
is
v_current_sal NUMBER;
v_error_nr NUMBER;
e_increaseTooLarge exception;
pragma exception_init(e_increaseTooLarge,-20999);
begin
...
exception
when increase_too_much then
v_error_nr := sqlcode;
insert into t_LogError (error_tx)values(i_empNo_nr||':'||v_error_nr);
raise;
end;
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.