How to avoid variable substitution in Oracle SQL Developer
When I try to execute this statement in Oracle SQL Developer 2.1 a dialog box "Enter Substitution Variable" pops up asking for a replacement value for TOBAGO,
update t set country = 'Trinidad and Tobago' where country = 'trinidad & tobago';
How can I avoid this without resorting to chr(38)
or u'trinidad \0026 tobago'
which both obscure the purpose of the statement?
Solution 1:
Call this before the query:
set define off;
Alternatively, hacky:
update t set country = 'Trinidad and Tobago' where country = 'trinidad &' || ' tobago';
From Tuning SQL*Plus:
SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.
Solution 2:
In SQL*Plus putting SET DEFINE ?
at the top of the script will normally solve this. Might work for Oracle SQL Developer as well.