#100CodeExamples – Dynamic values in a String: UTL_LMS

If you want to add dynamic values in a string with PL/SQL, you can concatenate it like this:

l_alertMessage := 'A bunch of "' || l_attacker ||
  '" is attacking with an estimated fleet of ' ||
  to_char(l_numOfShips) || ' ships';

But this is both, hard to read and tedious to write, because we have to cast everything which is not a varchar2/nvarchar2 explicitly with TO_CHAR.

There are, however, several nicer ways to achieve that goal. One way is the usage of UTL_LMS.

declare
  l_alertMessage varchar2(200) :=
    'A bunch of "%s" is attacking with ' ||
    'an estimated fleet of %d ships';
begin
  -- Works with values
  dbms_output.put_line(utl_lms.format_message(
    l_alertMessage,
    'Values', 10));

  -- Doesnt replace anything if no values are provided
  dbms_output.put_line(utl_lms.format_message(
    l_alertMessage));

  -- Replaces missing values with empty string
  dbms_output.put_line(utl_lms.format_message(
     l_alertMessage,
     'Not_all_values_set'));

  -- Works with NVARCHAR and BINARY_INTEGER types
  declare
    l_inputString nvarchar2(40) := 'NVARCHAR2/BINARY_INTEGER';
    l_numOfShips binary_integer := 25;
  begin
    dbms_output.put_line(utl_lms.format_message(
      l_alertMessage, l_inputString, l_numOfShips));
  end;

  -- Works with VARCHAR and subtypes of BINARY_INTEGER like PLS_INTEGER
  declare
    l_inputString varchar2(40) := 'VARCHAR2/PLS_INTEGER';
    l_numOfShips pls_integer := 75;
  begin
    dbms_output.put_line(utl_lms.format_message(
      l_alertMessage, l_inputString, l_numOfShips));
  end;

  -- Order is important
  declare
    l_inputString varchar2(40) := 'Wrong Order';
    l_numOfShips pls_integer := 122;
  begin
    dbms_output.put_line(utl_lms.format_message(
       l_alertMessage, l_numOfShips, l_inputString));
  exception when others then
    dbms_output.put_line('Wrong Order: ' || sqlerrm);
  end;

  -- Fails silently with INTEGER types
  declare
    l_inputString varchar2(40) := 'INTEGER';
    l_numOfShips integer := 13;
  begin
    dbms_output.put_line(utl_lms.format_message(
      l_alertMessage, l_inputString, l_numOfShips));
  end;

  -- Fails silently with NUMBER types
  declare
    l_inputString varchar2(40) := 'NUMBER';
    l_numOfShips number(10,0) := 34;
  begin
    dbms_output.put_line(utl_lms.format_message(
      l_alertMessage, l_inputString, l_numOfShips));
  end;

  -- You can escape % with doubling it
  dbms_output.put_line(utl_lms.format_message(
    'Probability to survive: %s%%', to_char(12.5)));
end;
/

Output:

A bunch of "Values" is attacking with an estimated fleet of 10 ships
A bunch of "%s" is attacking with an estimated fleet of %d ships
A bunch of "Not all values set" is attacking with an estimated fleet of ships
A bunch of "NVARCHAR2/BINARY_INTEGER" is attacking with an estimated fleet of 25 ships
A bunch of "VARCHAR2/PLS_INTEGER" is attacking with an estimated fleet of 75 ships
Wrong Order: ORA-06502: PL/SQL: numeric or value error
A bunch of "INTEGER" is attacking with an estimated fleet of ships
A bunch of "NUMBER" is attacking with an estimated fleet of ships
Probability to survive: 12.5%

You can find a full working example on LiveSQL.

Why I learned this

(This is a new question I’ll try to answer in every upcoming code-example during the challenge)

I am currently searching for ways to improve readability of messages which contain several dynamic parts. This might be one possibility, though the limits I currently see:

• Limited to VARCHAR2 and BINARY_INTEGER (no DATE or TIMESTAMP)
• No support for replacing a value occurring multiple times
• Positional provision of values is harder to read/maintain and more likely to become buggy