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
Top comments (0)