CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Janek Bogucki
Janek Bogucki

Reputation: 5123

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?

Upvotes: 143

Views: 248204

Answers (6)

Nick Craver
Nick Craver

Reputation: 630349

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.

Upvotes: 235

BIBD
BIBD

Reputation: 15384

While set define off works, you run into the problem of not being able to use Substitution Variables in the same query as an Ampersand. A clearer way would be to turn on the Escape character and then escape the Ampersand.

e.g.,

set escape \

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

The backslash (\) is the default escape character, but it seems to be turned off by default as well.

Upvotes: 0

Chamith
Chamith

Reputation: 129

If you use liquibase to run sql file, it will not give error. But for sql developer use this set define off; before ur sql sode

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

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';

Upvotes: 1

Dinesh Garud
Dinesh Garud

Reputation: 1

set scan off; Above command also works.

Upvotes: -3

diederikh
diederikh

Reputation: 25271

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

Upvotes: 16

Related Questions