Search code examples
oracleoracle-sqldeveloper

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

  • 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.