Escape ampersand in PL/SQL Developer

oracle escaping special-characters plsqldeveloper
Ray Booysen picture Ray Booysen · Aug 18, 2011 · Viewed 69.8k times · Source

I'm trying a simple INSERT statement against an Oracle database. One of the values is a VARCHAR2 field and the insert statement contains an ampersand. How do I do this? I've tried the following methods:

  1. Escape the & as \& with set escape on
  2. set scan off (this causes an ORA-00922 missing or invalid option error)
  3. set define off (this causes an ORA-00922 missing or invalid option error)

Any other ideas?

Answer

Ray Booysen picture Ray Booysen · Aug 18, 2011

How I solved it is escaping the & with another &.

For example:

INSERT INTO Foo (Bar) VALUES ('Up && Away');

Works nicely. Thanks for all the help

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
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=' ); …

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