Hey,
we have to support multilingual error messages for user defined exceptions. Our working prototype looks like this:
-- userdefinedexception package
create or replace package ude authid definer as
c_example_number constant pls_integer:=-20105;
c_example_msg_en constant utl.varchar2_m:='english error message...';
c_example_msg_es constant utl.varchar2_m:='mensaje de error...';
e_example exception;
pragma exception_init(e_example, c_example_number);
end;
/
-- example usage
begin
raise ude.e_example;
exception
when ude.e_example then
if sys_context('userenv','language') like 'ENGLISH%' then
raise_application_error(ude.c_example_number, ude.c_example_msg_en);
else
raise_application_error(ude.c_example_number, ude.c_example_msg_es);
end if;
end;
/Of course all the code inside the if statement can be put in a separate procedure like "raise_application_error_i18n"...
But what we really want and should be very comfortable is to raise the exception in this simple form:
begin
raise ude.e_example;
end;
and get a error message corresponding to the current NLS language setting...
How can we achieve that? How Oracle implement this internally?
If you want to attach an error message to your custom error code, you'll need to use raise_application_error anyway. With a plain raise, you'll get no message other than the error number.
So you may as well create a raise_application_error_i18n to handle the language logic, and call that in raise_application_error.
I'd consider storing the error messages in a table, mapping the code and language to the text. e.g.:
create table err_messages (
error_code integer,
language varchar2(100),
error_message varchar2(255)
);
This allows you to add/remove/change the messages without code changes. And replace (possibly many) if branches with:
select * from err_messages
where error_code = sqlcode
and language = sys_context('userenv','language');