Toad Thinks &string As Bind Variable

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&

Solution 1:

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

setdefine~;
select'drag&drop', ~column_name from~table_name;

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

Solution 2:

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/