How to escape ampersand in TOAD?

sql plsql toad
Utham Radipe picture Utham Radipe · Sep 11, 2013 · Viewed 37.5k times · Source

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?

Answer

Andrea picture Andrea · Nov 15, 2013

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;

Related questions

How to display a sys_refcursor data in TOAD's DataGrid

Please i need help. (I SEARCHED A lot and get more confused . ) I use Toad 9.7.25 and i made this procedure (in a package) PROCEDURE ReportaCC(pfcorte IN DATE, lcursor IN OUT SYS_REFCURSOR) IS BEGIN OPEN lcursor FOR select c1, …

Read More
Variables in TOAD scripts

I have a SQL script that is being executed in TOAD. Currently, I have it laid out with just statement after statement, thusly: select such-and-such from somewhere; delete other-thing from somewhere-else; And so on. Some of the where clauses end …

Read More
Passing varchar2 parameter to plsql procedure from the Select statement

Executing the following statement in Oracle Toad exec plsql_procedure(select 'somestring' from dual); trhows the following exception: ORA-06550: line 1, column 33: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( ) - + case mod new not null Procedure takes …

Read More