Escape ampersand in PL/SQL Developer

I'm trying a simple INSERT statement against an Oracle datab开发者_开发问答ase. One of the values is a VARCHAR2 field and the insert statement contains an ampersand. How do I do this? I've tried the following methods:

1. Escape the & as \& with set escape on
2. set scan off (this causes an ORA-00922 missing or invalid option error)
3. set define off (this causes an ORA-00922 missing or invalid option error)

Any other ideas?

At the bottom of a SQL window there is a button to disable/enable substitution variables:

How I solved it is escaping the & with another &.

For example:

INSERT INTO Foo (Bar) VALUES ('Up && Away');

Works nicely. Thanks for all the help

One of the features of PL/SQL Developer which it takes time to get familiar with is the plethora of different window types.

One of these is the COMMAND window, which is basically a SQL*Plus emulator. We can run SQL*Plus commands as well as SQL, so SET ESCAPE, SET DEFINE and indeed SET SCAN OFF work in that window.

Have you tried something like this?

INSERT INTO tablex VALUES ('Sid ' || '&' || ' Nancy');

Improving my first answer, your problem is related with PL/SQL Developer. If you execute your block in a PL/SQL Developer Command window, you could also use the standard SET DEFINE OFF, which works the same as in SQL*Plus.

the concat worked perfectly fine for me below is what i did in my select statement:

select FUND_NM
FROM PERFORMANCE
WHERE upper(FUND_DESC) in ('My L&' ||'L FUNDS')

I use chr(38) where I need an ampersand in PL/SQL.

addr:= 'mysite.com/order.aspx?no=5678' || CHR(38) || 'id=947';
--above line gives you 'mysite.com/order.aspx?no=5678&id=947';

This is just a SQL Editor issue. To resolve this just compile the procedure with SET DEFINE OFF; . Execution which is PL (executing in server) will not face this issue.