Every Oracle error has a message text, translated into many languages, with placeholders for names and values. UTL_LMS reads those texts, in any installed language, and formats strings with %s and %d placeholders, so PL/SQL can build messages the way Oracle does.
Code for This Guide
The main examples are in the examples/pkg-raw-text folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
rc := utl_lms.get_message(errnum, product, facility, language, message_out); utl_lms.format_message(format, arg1, arg2, ...) -- %s text, %d integer
For database errors the product is 'rdbms' and the facility 'ora'. GET_MESSAGE returns 0 when it finds the message.
Read Messages and Format Text
Example:
declare
v_msg varchar2(512);
v_rc pls_integer;
begin
v_rc := utl_lms.get_message(1403, 'rdbms', 'ora', null, v_msg);
dbms_output.put_line('error 1403: ' || v_msg);
v_rc := utl_lms.get_message(1, 'rdbms', 'ora', 'german', v_msg);
dbms_output.put_line('error 1, in German: ' || v_msg);
dbms_output.put_line(utl_lms.format_message('Flight %s is %d minutes late', 'NM150', 45));
end;
/Output:
error 1403: no data found error 1, in German: Unique Constraint (%s.%s) für Tabelle %s.%s mit Spalten (%s) verletzt Flight NM150 is 45 minutes late PL/SQL procedure successfully completed.
Error 1403 is no data found. Error 1 in German shows the template with its %s placeholders. FORMAT_MESSAGE fills %s with NM150 and %d with 45.
Fill an Oracle Message Template
Since the templates use the same placeholders, FORMAT_MESSAGE can fill them.
Example:
declare
v_msg varchar2(512);
v_rc pls_integer;
begin
v_rc := utl_lms.get_message(1, 'rdbms', 'ora', 'american', v_msg);
dbms_output.put_line('template: ' || v_msg);
dbms_output.put_line('filled: ' || utl_lms.format_message(v_msg, 'NIMBUS',
'BOOKINGS_PK', 'NIMBUS', 'BOOKINGS', 'BOOKING_ID'));
end;
/Output:
template: unique constraint (%s.%s) violated on table %s.%s columns (%s) filled: unique constraint (NIMBUS.BOOKINGS_PK) violated on table NIMBUS.BOOKINGS columns (BOOKING_ID) PL/SQL procedure successfully completed.
The five %s placeholders of ORA-00001 are filled in order, giving the message a user would see for a duplicate key on BOOKINGS.
Things to Know
- The language is an Oracle NLS language name such as 'german' or 'french'; NULL uses the session language.
- A language name that is not available does not fail: the message comes back in English and the return code is still 0.
- In FORMAT_MESSAGE, %% gives a literal percent sign.
Related Guides
Conclusion
UTL_LMS reads Oracle message texts in any installed language and formats strings with %s and %d placeholders. Use it for translated error texts and for building messages from templates.
