Oracle: Ampersands (&) in text/strings being parsed as parameters
While trying to specify a url with an ampersand inside a stored procedure I found TOAD (my favourite Oracle editor) asking me to specify a parameter. By default, Oracle will parse any string containing an & as a dynamic parameter within the text.
For example, a string of: ‘http://www.ibm.com/?fake=url&value=1’
will prompt you to specify parameter :VALUE
To change this default behaviour on a case-by-case scenario, run the following SQL first as part of the entire statement:
SET DEFINE OFF;
Another way to get around this is to hack the string into pieces that won’t get parsed as a parameter.
'http://www.ibm.com/?fake=url' || '&' || 'value=1'
