oracle utl_lms integer substitution with variables

Question

I've encountered an oddity using oracle's UTL_LMS.MESSAGE_FORMAT. When targeting %d replacements with variable substitutions, UTL_LMS is quietly failing to replace and disrupting exception-messaging in my call-stack.

UTL_LMS.FORMAT_MESSAGE calls for VARCHAR params, but type-conversion in the doc(11gR2) examples seems like this would be compatible with integer-variables. However, my %d are never being replaced.

I hoped for some ideas/advice. An example of the failure is below.

Running the following:

DECLARE
  C_INTRANSITIVE_VERB CONSTANT VARCHAR2(50) := 'breakdance';
  C_SUBJECT_NOUN CONSTANT VARCHAR2(50) := 'hobbit';
  C_PREPOSITION_OBJECT CONSTANT VARCHAR2(50) := 'wookie';
  C_MULTIPLIER CONSTANT INTEGER := 19;
  C_TEMPLATE CONSTANT VARCHAR2(400) := 'The %s likes to %s with the %s %d times a day.';
BEGIN
  DBMS_OUTPUT.PUT_LINE('My integer is: '|| C_MULTIPLIER);
  DBMS_OUTPUT.PUT_LINE(
      UTL_LMS.FORMAT_MESSAGE(C_TEMPLATE,C_SUBJECT_NOUN,C_INTRANSITIVE_VERB,C_PREPOSITION_OBJECT,C_MULTIPLIER)
  );
END;
/

Yields:

My integer is: 19

The hobbit likes to breakdance with the wookie times a day.

However, the below converts the raw int fine:

DECLARE
  C_INTRANSITIVE_VERB CONSTANT VARCHAR2(50) := 'breakdance';
  C_SUBJECT_NOUN CONSTANT VARCHAR2(50) := 'hobbit';
  C_PREPOSITION_OBJECT CONSTANT VARCHAR2(50) := 'wookie';
  C_MULTIPLIER CONSTANT INTEGER := 19;
  C_TEMPLATE CONSTANT VARCHAR2(400) := 'The %s likes to %s with the %s %d times a day.';
BEGIN
  DBMS_OUTPUT.PUT_LINE('My integer is: '|| C_MULTIPLIER);
  DBMS_OUTPUT.PUT_LINE(
      UTL_LMS.FORMAT_MESSAGE(C_TEMPLATE,C_SUBJECT_NOUN,C_INTRANSITIVE_VERB,C_PREPOSITION_OBJECT,19)
  );
END;
/

My integer is: 19

The hobbit likes to breakdance with the wookie 19 times a day.

The below workaround is undesirable.

DECLARE
  C_INTRANSITIVE_VERB CONSTANT VARCHAR2(50) := 'breakdance';
  C_SUBJECT_NOUN CONSTANT VARCHAR2(50) := 'hobbit';
  C_PREPOSITION_OBJECT CONSTANT VARCHAR2(50) := 'wookie';
  C_MULTIPLIER CONSTANT INTEGER := 19;
  C_TEMPLATE CONSTANT VARCHAR2(400) := 'The %s likes to %s with the %s %d times a day.';
BEGIN
  DBMS_OUTPUT.PUT_LINE('My integer is: '|| C_MULTIPLIER);
  DBMS_OUTPUT.PUT_LINE(
      UTL_LMS.FORMAT_MESSAGE(C_TEMPLATE,C_SUBJECT_NOUN,C_INTRANSITIVE_VERB,C_PREPOSITION_OBJECT,TO_CHAR(C_MULTIPLIER))
  );
END;
/

My integer is: 19

The hobbit likes to breakdance with the wookie 19 times a day.

1. Why does FORMAT_MESSAGE accept raw integers, but fail on INTEGER-typed (or general NUMBER-typed) variables?
2. Is there is a better prepackaged(11g) dbms message-formatter already available?
3. Any other advice for clean message-formatting in oracle 11g? I would consider wrapping java's MessageFormat/String#format(), or making an an FORMAT_MESSAGE delegate that handles char type-conversion, but would prefer to avoid this for all the databases that would require it, and I'm not too keen on "just remembering" to_char() everywhere as failures are fairly quiet.

Thanks!

Answer 1

Looking at the documentation at

https://docs.oracle.com/database/121/ARPLS/u_lms.htm#ARPLS71196

The example specifies a PLS_INTEGER rather than a Integer or Number. I have tested this out and using a PLS_INTEGER works where an Integer does not.

I am aware this is not an ideal solution and I am not sure if defining your variables as pls_integer would be an option for you. But may be of some help.

DECLARE
  C_INTRANSITIVE_VERB CONSTANT VARCHAR2(50) := 'breakdance';
  C_SUBJECT_NOUN CONSTANT VARCHAR2(50) := 'hobbit';
  C_PREPOSITION_OBJECT CONSTANT VARCHAR2(50) := 'wookie';
  C_MULTIPLIER CONSTANT PLS_INTEGER := 19;
  C_TEMPLATE CONSTANT VARCHAR2(400) := 'The %s likes to %s with the %s %d times a day.';
BEGIN
  DBMS_OUTPUT.PUT_LINE('My integer is: '|| C_MULTIPLIER);
  DBMS_OUTPUT.PUT_LINE(
      UTL_LMS.FORMAT_MESSAGE(C_TEMPLATE,C_SUBJECT_NOUN,C_INTRANSITIVE_VERB,C_PREPOSITION_OBJECT,C_MULTIPLIER)
  );
END;

Gives the output

My integer is: 19
The hobbit likes to breakdance with the wookie 19 times a day.