Generic and detailed custom Oracle exceptions
How do you handle custom exceptions in your PL/SQL code? I recently observed the practice of raising an ORA-20001 code with a custom error message every single time and hated it. Why couldn’t we have a documented repository with all these exceptions? Why couldn’t we have some standardized and generic messages to fit to different scenarios? Let’s see a simple solution to obtain that.
First, a package holding all custom exceptions with codes starting from ORA-20001 and parametrized messages:
e_UserAlreadyHasRole exception;
e_UserAlreadyHasRole constant integer := -20001;
e_UserAlreadyHasRole constant varchar2(1000) := 'User %1 is currently %2 and cannot get a new role without deleting or updating the old one.';
Second, raising an exception using a custom function to display the message:
exception
when exceptions.e_UserAlreadyHasRole then
raise_application_error(exceptions.e_UserAlreadyHasRole_code,
exceptions.error_text(exceptions.e_UserAlreadyHasRole_msg, v_user_name || ';' || v_role_name));
And let’s see the final result. Instead of:
The user has currently a role and cannot get a new one without deleting or updating the old one.
we have:
User POPESCU is currently MANAGER and cannot get a new role without deleting or updating the old one.
Better, right?
Oh, and here is the function that parametrizes the error messages:
function error_text(pi_error_msg in varchar2, pi_string in varchar2) return varchar2 is
l_percents integer;
l_commas integer;
l_error_msg varchar2(1000);
l_value varchar2(1000);
l_percent varchar2(1) := '%';
l_separator varchar2(1) := ';';
l_idx pls_integer := 1;
begin
l_error_msg := pi_error_msg;
l_percents := regexp_count(pi_error_msg, l_percent);
l_commas := regexp_count(pi_string, l_separator);
if l_percents = l_commas + 1 then
if l_commas >= 0 then
for i in 1 .. l_commas + 1 loop
l_value := regexp_substr(pi_string, '[^' || l_separator || ']+', 1, l_idx);
l_error_msg := replace(l_error_msg, '%' || l_idx, l_value);
l_idx := l_idx + 1;
end loop;
end if;
else
l_error_msg := l_error_msg || ' (internal note: please set the correct exception parameters)';
end if;
return l_error_msg;
end error_text;