TOAD thinks &String as bind variable

Question

I am developing some ETL with Oracle Data Integrator and sometimes test parts of my code by TOAD. Today I had a problem with TOAD I had a line like

AND column_value like('DEV&PROD')

when I tried to run the SQL which includes filter above, TOAD ask for the value of PROD, it thought like PROD is a bind or substitution variable. Is there any option in TOAD settings to turn this feature of. I am using bind variable with a column (:), and my data includes & so I need to use it.

This code works in ODI perfectly, I only need a solution for TOAD. Thanks in advance.

Egor Skriptunoff · Accepted Answer · 2013-02-25T17:39:49.783

4

1) start your script with set define off; (and run whole script with F5 key)
or
2) use 'DEV&'||'PROD' instead of 'DEV&PROD'
or
3) set another prefix symbol for variables

set define ~;
select 'drag&drop', ~column_name from ~table_name;

(you will be prompted for column_name and table_name, but not for 'drop')

edited Feb 25 '13 at 17:39

answered Feb 25 '13 at 12:11

Egor Skriptunoff

23,359
2
34
64

First way works perfectly for this case thanks, but when I try to use also bind variables with column, it also ignores these variables. And I would not like to split my string into more than one pieces. So is there anyway that I can use :bind_variable, and ignore &not_a_variable and do not split my strings. Thanks in advance. – Canburak Tümer Feb 25 '13 at 13:10
@CanburakTümer - yes, see appended solution #3 – Egor Skriptunoff Feb 25 '13 at 17:40

score 0 · Answer 2 · answered Feb 25 '13 at 17:37

In addition - will work in any tool or SQL prompt:

SELECT ('DEV'||'&'||'PROD') val FROM dual
/

-- Q-quote operator introduced in Oracle 10g --
SELECT 'DEV' || q'[&]'||'PROD' AS val FROM dual
/

Using Egor's or my examples - copy/paste, enter 1 for :bind_var :

SELECT 'DEV&'||'PROD' val FROM dual
 WHERE :bind_var = 1
/

TOAD thinks &String as bind variable

2 Answers2

Linked