Escaping ampersand. Getting rid of "Enter Substitution Variable" message.

If you want to bypass "Enter  Substitution Variable" message when there is '&' or '&&' sign on your oracle SQL then all you need to do is add following line at the top of your SQL.


Exmaple ;


This SQL has ampersand and it will prompt to enter a  substitution variable.


select 'dfd&dfd' from dual
where DUMMY ='E&AMPLE';


This issue can be fixed by adding following line into you sql.


SET DEFINE OFF
select 'dfd&dfd' from dual
where DUMMY ='E&AMPLE'


Hope this helps.