102

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.

gitsitgo
  • 6,589
  • 3
  • 33
  • 45
ian_scho
  • 5,906
  • 9
  • 35
  • 51

8 Answers8

143

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

BuZZ-dEE
  • 6,075
  • 12
  • 66
  • 96
Neil Kodner
  • 2,901
  • 3
  • 27
  • 36
71

|| chr(38) ||

This solution is perfect.

Gaffi
  • 4,307
  • 8
  • 43
  • 73
Aseem
  • 711
  • 5
  • 2
  • 3
    This gets the job done, but is a little clunky when dealing with pre-existing strings. – aglassman Sep 05 '13 at 14:43
  • For me SET DEFINE OFF did not work. Your solution worked will. Thank you... – Ashok kumar Mar 31 '20 at 07:06
  • Where can I find a complete list of chr() characters? I have a similar problem but in my case I'm looking to escape a ( ' ) character. – grego Mar 30 '21 at 23:35
  • @user7587050 You can use any ASCII table. They are readily available on the Internet, e.g. on Wikipedia https://en.wikipedia.org/wiki/ASCII – Frank Schmitt Jun 21 '21 at 07:29
33

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
RC.
  • 27,409
  • 9
  • 73
  • 93
17
insert into AGREGADORES_AGREGADORES (IDAGREGADOR,NOMBRE,URL)
values (2,'Netvibes',
'http://www.netvibes.com/subscribe.php?type=rss' || chr(38) || 'amp;url=');
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • This is a much better general solution that is independent of user priveleges (i.e. when users are not allowed to SET DEFINE OFF) and for when users want to specify both ampersands in text and defined variables in the same SQL command. – Fuzzy Analysis Oct 31 '13 at 22:26
17
SELECT 'Free &' || ' Clear' FROM DUAL;
drj
  • 193
  • 1
  • 6
7

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

Gaffi
  • 4,307
  • 8
  • 43
  • 73
Izo
  • 71
  • 1
  • 1
  • 1
    The ampersand need only be at the end of the string, saving half the concatenation. `select 'one&' || 'two' from dual` – durette Jul 24 '19 at 13:57
1

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.

Terrible Tadpole
  • 607
  • 6
  • 20
  • 5
    I read on Ask Tom (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1629001802771) that "SET ESCAPE is a sqplus'ism". So this would only be the real answer if the question were about SQL*Plus. – Karl Kieninger Oct 03 '14 at 13:16
  • 1
    @KarlKieninger: For the most part, you're right. But the whole idea of a substitution variable is *also* an sqlplus-ism. Since SQL Developer is recognizing & as a substitution variable (which is one sqlplus-ism) I guess I'm not surprised it might also recognizing the SET ESCAPE command (another sqlplus-ism). According to the SQL Developer User Guide, its "Script Runner" does recognize a limited set of SQL*Plus features, including substitution vars (at least vers. 19). However, it gave error messages when I tried SET ESCAPE. But it did accept SET DEFINE OFF. Curiouser and curiouser. – Syntax Junkie Jul 15 '23 at 20:22
0

add this before your request

set define off;
Helali
  • 196
  • 1
  • 6