Wednesday, 30 November 2016

Stop typing and generate you substitution code

Some years ago I attended a presentation of John Scott in which he demonstrated the use of substitution for producing a string from a number of variables.

Up to that moment I used concatenation for generating strings from variables:

l_html &nbsp;:= '<input id="'||l_id||'" onclick="test('''||l_value||''')" type="text" />';  

Even a relative simple expression like this is hard to read. Moreover it is not easy to see whether the quotes are placed at the right position and quantity.

So John's solution to this is to start with a string with delimited placeholders and replacing the placeholders with their value one by one. So the code gets:

l_html := q'!<input id="#ID#" onclick="test('#VALUE#')" type="text" />!';  
l_html := replace(l_html,'#ID#',l_id);  
l_html := replace(l_html,'#VALUE#',l_value);  

This code is much more readable and the html can be checked easily.
I have been using this design pattern a lot since then.

But the typing annoys me. Typically something to automate! So this morning, when I was getting ready to type a list of replace statements for the 1001st time I decided to create a small tool to generate these lines.

Paste the string with placeholders into the form and change the other parameters to your preferences. Press generate and you can copy the resulting code into your PL/SQL.
The generation presumes that the names of the variables containing the values to be replaced are equal to the placeholder names. Doing this also improves the readability of the code.

The utility can be used online at

Happy apexing!


koyli said...

Nice post - I've typed code like that many, many times over.

Recently though I have been using the UTL_LMS package to format strings - if you replace the #ID# and #VALUE# placeholders with %s placeholders and call the UTL_LMS.FORMAT_MESSAGE function to perform the substitution, you can avoid needing multiple REPLACE statements.

Tried to add and amend the code from your original post but blogger doesn't like those tags in comments !

Dick Dral said...

Thanks for the tip. For me there are some drawbacks to this solution:
- the %s in the template have no indication of their meaning
- because the substitution parameters are not named they should be exactly in the right order
- a parameter cannot be reused

Danielle said...

I enjjoyed reading this