How to avoid variable substitution in Oracle SQL Developer

Question

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?

That's strange, when I attempted to run a query exactly like that in SQL developer 2.1 I did not get the replacement variable window? (And my defines are most certainly set to on) — wasatz, Feb 25 '10 at 12:55

score 228 · Accepted Answer · edited Apr 11 '12 at 09:25

228

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.

edited Apr 11 '12 at 09:25

Álvaro González

142,137
41
261
360

answered Feb 25 '10 at 12:46

Nick Craver

623,446
136
1,297
1,155

11

What (else) does `set define off;` do? – Xonatron Mar 16 '12 at 18:54
SET DEFINE OFF: `Invalid "set define" command` – gene b. Aug 26 '22 at 17:43

score 16 · Answer 2 · answered Feb 25 '10 at 12:49

16

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

answered Feb 25 '10 at 12:49

diederikh

25,221
5
36
49

2

SET DEFINE ? does suppress the variable substitution behaviour in SQL Developer 2.1. As noted by Nick SET DEFINE OFF also works. – Janek Bogucki Feb 25 '10 at 13:03
1

set define works but the DBA wont allow to use it sometimes i dont know why – Nikhil S Oct 12 '18 at 18:14
Doesn't work. `Invalid "set define" command` – gene b. Aug 26 '22 at 17:43

Nikhil S · Answer 3 · 2018-10-13T20:13:26.430

1

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

edited Oct 13 '18 at 20:13

answered Oct 08 '18 at 05:51

Nikhil S

3,786
4
18
32

My question was how this could be done without using `chr(38)`. – Janek Bogucki Oct 12 '18 at 17:29
@JanekBogucki take it it correct now without using CHAR(38) – Nikhil S Oct 12 '18 at 18:13
i gave you a different answer as you already have green ticked answer but you downvoted – Nikhil S Oct 12 '18 at 18:26
Mate you cannot imagine what an agony you solved for me, sql developer does not accept set define off when you run a script. This the solution for me – BrokenFrog Sep 24 '19 at 21:05

score 0 · Answer 4 · answered Aug 06 '21 at 05:43

0

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

answered Aug 06 '21 at 05:43

Chamith

69
10

score -3 · Answer 5 · answered Aug 08 '16 at 07:16

-3

set scan off; Above command also works.

answered Aug 08 '16 at 07:16

Dinesh Garud

1

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you earn sufficient reputation you will be able to comment on any post. If you have a related but different question, ask a new question referencing this one if it will help provide context. – Rohan Khude Aug 08 '16 at 07:36

How to avoid variable substitution in Oracle SQL Developer

5 Answers5

Linked

Related