Oracle pl-sql escape character (for a '&')

Question

While attempting to execute SQL insert statements using Oracle SQL Developer I keep generating a "Enter substitution value" prompt:

Insert into AGREGADORES_AGREGADORES (IDAGREGADOR,NOMBRE,URL) values (2,'Netvibes','http://www.netvibes.com/subscribe.php?type=rss\&url=');

I've tried escaping the special character in the query using the '\' above but I still can't avoid the ampersand, '&', causing a string substitution.

Go ahead punks, make my day :)

Answer 1

insert into AGREGADORES_AGREGADORES (IDAGREGADOR,NOMBRE,URL)
values (2,'Netvibes',
'http://www.netvibes.com/subscribe.php?type=rss' || chr(38) || 'amp;url=');

Answer 2

Set the define character to something other than &

SET DEFINE ~
create table blah (x varchar(20));
insert into blah (x) values ('blah&amp');
select * from blah;

X                    
-------------------- 
blah&amp 

Answer 3

the & is the default value for DEFINE, which allows you to use substitution variables. I like to turn it off using

SET DEFINE OFF

then you won't have to worry about escaping or CHR(38).

Answer 4

|| chr(38) || This solution is perfect.