Oracle PL/SQL - Including error messages in user-defined exceptions

Introduction

You can specify error messages.

The syntax to append error message to error:

raise_application_error(<exception code>,<error message>);

You can only specify error messages for exceptions that have already been assigned a code.

procedure p_validateSalary(i_empNo_nr NUMBER, i_new_sal_tx NUMBER)
  is
    v_current_sal NUMBER;
    e_increaseTooLarge EXCEPTION;                                         
    pragma exception_init (e_increaseTooLarge,-20999)                     
begin
     select salary into v_current_sal
     from emp
     where empNo=i_empNo_nr;
     
     if (i_newsal_nr/v_current_sal)*100>300
     then
         raise_application_error (-20999, 'Cannot triple salary for employee #'||i_empNo);
     end if;

exception
    when e_increaseTooLarge then
       insert into t_logError ...
       raise;
end;

Related Topic