Oracle utl_lms whole replacement with variables

Question

Oracle utl_lms whole replacement with variables

I ran into weirdness using oracle UTL_LMS.MESSAGE_FORMAT. When targeting to% d replaces lookup variables, UTL_LMS cannot calmly replace and disable exception messaging in my call stack.

UTL_LMS.FORMAT_MESSAGE calls the VARCHAR parameters, but the type conversion in the doc (11gR2) examples looks like it will be compatible with integer variables. However, my% d is never replaced.

I was hoping for some ideas / tips. The following is an example of a failure.

Doing 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;
/

Productivity:

My whole number: 19
The hobbit loves break dance with packs once a day.

However, the bottom line converts the value of 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 whole number: 19
The hobbit loves break dancing with Wookiee 19 times a day.

A workaround below 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;
/