Oracle SQL escape character (for a ‘&’)

While attempting to execute SQL insert statements using Oracle SQL Developer I keep generating an “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.

Answer

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).