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!

Ep

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.

Welladvised

Thanks Shaun for digging in to a post open this long. While it would of course be preferable if the function just handled type conversions across the numeric types, and while binary integers won't always work, this fits with the observed pattern and gives the behavior some useful context. It also gives some more to look into. I can accept that. Thanks – Ep