Posted on • Originally published atcleandatabase.wordpress.com on
#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 ofUTL_LMS.
declarel_alertMessagevarchar2(200):='A bunch of "%s" is attacking with '||'an estimated fleet of %d ships';begin-- Works with valuesdbms_output.put_line(utl_lms.format_message(l_alertMessage,'Values',10));-- Doesnt replace anything if no values are provideddbms_output.put_line(utl_lms.format_message(l_alertMessage));-- Replaces missing values with empty stringdbms_output.put_line(utl_lms.format_message(l_alertMessage,'Not_all_values_set'));-- Works with NVARCHAR and BINARY_INTEGER typesdeclarel_inputStringnvarchar2(40):='NVARCHAR2/BINARY_INTEGER';l_numOfShipsbinary_integer:=25;begindbms_output.put_line(utl_lms.format_message(l_alertMessage,l_inputString,l_numOfShips));end;-- Works with VARCHAR and subtypes of BINARY_INTEGER like PLS_INTEGERdeclarel_inputStringvarchar2(40):='VARCHAR2/PLS_INTEGER';l_numOfShipspls_integer:=75;begindbms_output.put_line(utl_lms.format_message(l_alertMessage,l_inputString,l_numOfShips));end;-- Order is importantdeclarel_inputStringvarchar2(40):='Wrong Order';l_numOfShipspls_integer:=122;begindbms_output.put_line(utl_lms.format_message(l_alertMessage,l_numOfShips,l_inputString));exceptionwhenothersthendbms_output.put_line('Wrong Order: '||sqlerrm);end;-- Fails silently with INTEGER typesdeclarel_inputStringvarchar2(40):='INTEGER';l_numOfShipsinteger:=13;begindbms_output.put_line(utl_lms.format_message(l_alertMessage,l_inputString,l_numOfShips));end;-- Fails silently with NUMBER typesdeclarel_inputStringvarchar2(40):='NUMBER';l_numOfShipsnumber(10,0):=34;begindbms_output.put_line(utl_lms.format_message(l_alertMessage,l_inputString,l_numOfShips));end;-- You can escape % with doubling itdbms_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 shipsA bunch of "%s" is attacking with an estimated fleet of %d shipsA bunch of "Not all values set" is attacking with an estimated fleet of shipsA bunch of "NVARCHAR2/BINARY_INTEGER" is attacking with an estimated fleet of 25 shipsA bunch of "VARCHAR2/PLS_INTEGER" is attacking with an estimated fleet of 75 shipsWrong Order: ORA-06502: PL/SQL: numeric or value errorA bunch of "INTEGER" is attacking with an estimated fleet of shipsA bunch of "NUMBER" is attacking with an estimated fleet of shipsProbability to survive: 12.5%You can find a full working example onLiveSQL.
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)
For further actions, you may consider blocking this person and/orreporting abuse
