sqloracle-databaseescapingoracle-sqldeveloper

Reputation: 6056

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\&amp;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.

Upvotes: 104

Answers (8)

Neil Kodner

Reputation: 2956

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

Upvotes: 149

Helali

Reputation: 196

add this before your request

set define off;

Upvotes: 0

Terrible Tadpole

Reputation: 634

The real answer is you need to set the escape character to '\': SET ESCAPE ON

The problem may have occurred either because escaping was disabled, or the escape character was set to something other than '\'. The above statement will enable escaping and set it to '\'.

None of the other answers previously posted actually answer the original question. They all work around the problem but don't resolve it.

Upvotes: 1

Aseem

Reputation: 771

|| chr(38) ||

This solution is perfect.

Upvotes: 77

Izo

Reputation: 71

select 'one'||'&'||'two' from dual

Upvotes: 7

drj

Reputation: 301

SELECT 'Free &' || ' Clear' FROM DUAL;

Upvotes: 17

RC.

Reputation: 28267

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

Upvotes: 33

Jeffrey Kemp

Reputation: 60312

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

Upvotes: 17

Oracle SQL escape character (for a &#39;&amp;&#39;)

Answers (8)

Related Questions

Oracle SQL escape character (for a '&')