How to Format Oracle Error Messages with UTL_LMS

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

• How to Use SQLCODE in PL/SQL Exception Handlers
• How to Read the Call Stack with UTL_CALL_STACK

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.