Oracle SQL escape character (for a '&')

sql oracle escaping oracle-sqldeveloper
ian_scho picture ian_scho · Jul 16, 2009 · Viewed 278.8k times · Source

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

Neil Kodner picture Neil Kodner · Jul 20, 2009

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

Related questions

Escaping ampersand character in SQL string

I am trying to query a certain row by name in my sql database and it has an ampersand. I tried to set an escape character and then escape the ampersand, but for some reason this isn't working and I'm …

Read More
How to insert a string which contains an "&"

How can I write an insert statement which includes the & character? For example, if I wanted to insert "J&J Construction" into a column in the database. I'm not sure if it makes a difference, but I'm using …

Read More
How to handle a single quote in Oracle SQL

How do I insert a record in a column having varchar data type having single quote in it? Example: first name is ROBERT and last name is D'COSTA

Read More