159
UTL_LMS

UTL_LMS retrieves and formats error messages in different languages.

This chapter contains the following topics:

• Using UTL_LMS
  • Security Model
• Summary of UTL_LMS Subprograms

---

Using UTL_LMS

• Security Model

---

Security Model

This package must be created as the user SYS.

---

Summary of UTL_LMS Subprograms

Table 159-1  UTL_LMS Package Subprograms

Function	Description
FORMAT_MESSAGE Function	Formats a retrieved error message
GET_MESSAGE Function	Retrieves an error message based on error number, product, facility, language, and message specified

---

FORMAT_MESSAGE Function

This function formats a message retrieved by the GET_MESSAGE function and returns the formatted message. If the function fails, then it returns a NULL result.

The following table shows special characters that can be used in the format string.

Special Character	Description
'%s'	Substitute the next string argument
'%d'	Substitute the next integer argument
'%%'	Represents the special character %

Syntax

UTL_LMS.FORMAT_MESSAGE( format IN VARCHAR2 CHARACTER SET ANY_CS,
                        args   IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL)
RETURN VARCHAR2 CHARACTER SET format%CHARSET;

Parameters

Table 159-2  FORMAT_MESSAGE Procedure Parameters

Parameter	Description
format	Specifies the string to format
args	Specifies the list of arguments

Examples

DECLARE
   s varchar2(200);
   i  pls_integer;
BEGIN
   i:= utl_lms.get_messsage(26052, 'rdbms', 'ora', 'french', s);
   dbms_output.put_line('before format, message is: '||s);
   dbms_output.put_line('formatted message is: '||utl_lms.format_message(s, 9, 
'my_column_name');
END;
/

The following is an unformatted message:

Type %d non pris en charge pour l'expression SQL sur la colonne %s.

The following is the formatted message:

Type 9 non pris en charge pour l'expression SQL sur la colonne my_column_name.

---

GET_MESSAGE Function

This function retrieves an Oracle error message. The user can define user-specific error messages with the lmsgen utility.

It returns 0 when it is successful. It returns -1 when it fails.

Syntax

UTL_LMS.GET_MESSAGE( errnum    IN PLS_INTEGER,
                     product   IN VARCHAR2,
                     facility  IN VARCHAR2,
                     language  IN VARCHAR2,
                     message   OUT NOCOPY VARCHAR2CHARCTER SET ANY_CS)
RETURN PLS_INTEGER;

Parameters

Table 159-3  GET_MESSAGE Function Parameters

Parameter	Description
errnum	Specifies the error number. Example: '972' (for ORA-00972)
product	Specifies the product to which the error message applies Example: 'rdbms'
facility	Specifies the error message prefix Example: 'ora'
language	Specifies the language of the message. The parameter is case-insensitive. The default is NULL, which causes GET_MESSAGE to use the value of the NLS_LANGUAGE session parameter.
message	Specifies the output buffer for the retrieved message

Usage Notes

If the language parameter is set to NULL, then the value of the NLS_LANGUAGE session parameter is used as the default.

Examples

DECLARE
  s varchar2(200);
  i pls_integer;
BEGIN
   i:=utl_lms.get_message(601, 'rdbms', 'oci', 'french', s);
   dbms_output.put_line('OCI--00601 is: '||s);
END
/

The following output results:

OCI--00601 is: Echec du processus de nettoyage.