24

When I try to insert a value which has '&' in TOAD I get a prompt for a substitution variable.

So when I try:

insert into x values('hello & world');

it throws a prompt for substituting the variable 'world'.

I have tried right clicking in the editor and disabling prompt for substitution variables but this seems to work only for Execute Statement (F9) It doesn't work for Execute as script(F5).

I am also aware of using chr(38) like:

insert into x values('hello '||chr(38)||'world');

but I do not want this. Is there something else that will help me run the script?

Andrea
  • 11,801
  • 17
  • 65
  • 72
Utham Radipe
  • 343
  • 1
  • 2
  • 5
  • Possible duplicate: http://stackoverflow.com/questions/5020709/query-help-string-in-where-clause-has-character –  Sep 11 '13 at 07:52
  • I am looking for a different solution. The accepted answer says about disabling prompt for substitution variables. But as mentioned in my question I have tried this. – Utham Radipe Sep 11 '13 at 07:56
  • 1
    The link I provided has also other answers, not only the accepted solution. –  Sep 11 '13 at 07:58

4 Answers4

37

Try putting set define off at the beginning of your script. It should work with F5:

set define off;
insert into x values('hello & world'); 
set define on;
tbarderas
  • 55
  • 6
Andrea
  • 11,801
  • 17
  • 65
  • 72
11

In TOAD version 12.120.39 Right click in Editor and Click in Substitution Variable Prompting and select none

This worked for me.

Sujeet
  • 111
  • 1
  • 3
8

set define off doesn't work in TOAD just in sqlplus.

You can disable substitution variable support by right-clicking in the Editor and unchecking the “Prompt for Substitution Variables” item. It works with F9 execution but not with F5 (script) execution. With F5 you need to add "set define off" in the first line then launch with F5!! (hope this help it was very hard to find for me)

uffa14
  • 155
  • 1
  • 8
0

I use toad 16.1 and disabling on editor "Substitution Variable Prompting = none" works all the time

LNM
  • 1