#100CodeExamples – Dynamic values in a String: UTL_LMS
Tags: 100codeexamples, plsql, oracle
Published: 2018-12-11 22:33:33

---
title: #100CodeExamples – Dynamic values in a String: UTL_LMS
published: true
tags: #100CodeExamples,PL/SQL,Oracle
canonical_url: https://cleandatabase.wordpress.com/2018/12/11/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:

```sql
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](https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_lms.htm#i999311).

```sql
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:

```plain
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](https://livesql.oracle.com/apex/livesql/s/hn6oxzoqm3aesk0wmgpipnqqp).

### 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