5

I am running an SQL (Oracle) statement like that

select * from table 
where table_id in ('265&310', '266&320')

While running through TOAD, it consider & as some variable placeholder and it asks for its value. If it was for 1-2 place holders then I could have set it in TOAD but the in clause has like 200 of strings.

How to put this query?

I want to export the DATASET as SQL INSERT statement, so I can't use this in SQL-PLUS.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
x.509
  • 2,205
  • 10
  • 44
  • 58

3 Answers3

7
SET DEFINE OFF;

Will work to turn the prompting for variable off..

or 

SET ESCAPE ON;
SELECT 'blah \& blah' AS DES FROM DUAL;
Randy
  • 16,480
  • 1
  • 37
  • 55
  • is there any optino to set define off or set define ~ in TOAD? – x.509 Feb 16 '11 at 19:03
  • That works when running as a script, but TOAD uses it's own parser for running individual statments. Fortunately, you can disable substitution from the Options menu, as I showed in my answer. – Michael Broughton Feb 16 '11 at 19:21
5

In TOAD, you can disable the prompt for substitution variables from the options dialog:

You need to uncheck: View –> Toad Options –> Execute/Compile –> Prompt for Substitution variables.

Michael Broughton
  • 4,045
  • 14
  • 12
3

You can escape the ampersand character by using concatenation, like this:

select * from table 
where table_id in ('265' || '&' || '310', '266' || '&' || '320')
Tommi
  • 8,550
  • 5
  • 32
  • 51