[oracle] How to avoid variable substitution in Oracle SQL Developer with 'trinidad & tobago'

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?

This question is related to oracle oracle-sqldeveloper

The answer is


set scan off; Above command also works.


In SQL*Plus putting SET DEFINE ? at the top of the script will normally solve this. Might work for Oracle SQL Developer as well.


this will work as you asked without CHAR(38):

update t set country = 'Trinidad and Tobago' where country = 'trinidad & '|| 'tobago';

create table table99(col1 varchar(40));
insert into table99 values('Trinidad &' || '  Tobago');
insert into table99 values('Trinidad &' || '  Tobago');
insert into table99 values('Trinidad &' || '  Tobago');
insert into table99 values('Trinidad &' || '  Tobago');
SELECT * FROM table99;

update table99 set col1 = 'Trinidad and Tobago' where col1 = 'Trinidad &'||'  Tobago';