27

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?

Makoto
  • 104,088
  • 27
  • 192
  • 230
Ray Booysen
  • 28,894
  • 13
  • 84
  • 111
  • 3
    I've "solved" it using the chr(38) solution in this question: http://stackoverflow.com/questions/1137354/oracle-pl-sql-escape-character-for-a but would like something more graceful. – Ray Booysen Aug 18 '11 at 07:23
  • Are you trying to execute the insert via a SQL window or a command window? – Adam Paynter Aug 18 '11 at 08:46
  • Ray, have you tried setting the define to another character rather than just setting it off entirely? Not sure if it would help as I don't use PLSQL Developer but it might... – Ollie Aug 18 '11 at 09:01
  • SQL window. This is especially important as our deployment team (for good or bad) follows this procedure too. – Ray Booysen Aug 18 '11 at 09:01
  • @Ollie, yes I did. same result. – Ray Booysen Aug 18 '11 at 09:02
  • Only the Command window emulates the SQL*Plus `set` variables, hence the syntax errors in your SQL window. Also there is no 'escape' character in SQL (or in PL/SQL Developer) except as part of a `like` expression, which is why your \ isn't recognised. – William Robertson Nov 15 '15 at 11:03

7 Answers7

33

At the bottom of a SQL window there is a button to disable/enable substitution variables:

enter image description here

William Robertson
  • 15,273
  • 4
  • 38
  • 44
27

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

Ray Booysen
  • 28,894
  • 13
  • 84
  • 111
13

One of the features of PL/SQL Developer which it takes time to get familiar with is the plethora of different window types.

One of these is the COMMAND window, which is basically a SQL*Plus emulator. We can run SQL*Plus commands as well as SQL, so SET ESCAPE, SET DEFINE and indeed SET SCAN OFF work in that window.

APC
  • 144,005
  • 19
  • 170
  • 281
12

Have you tried something like this?

INSERT INTO tablex VALUES ('Sid ' || '&' || ' Nancy');

Improving my first answer, your problem is related with PL/SQL Developer. If you execute your block in a PL/SQL Developer Command window, you could also use the standard SET DEFINE OFF, which works the same as in SQL*Plus.

Aitor
  • 3,309
  • 2
  • 27
  • 32
  • I don't think that is any more graceful than the `chr(38)` solution Ray mentions. – APC Aug 18 '11 at 08:52
  • propably, but it's the one that I used when I faced a similar problem a year ago. In http://www.orafaq.com/wiki/SQL_FAQ there are some nice solutions too in SQL*Plus, like SET ESCAPE '\', SET DEFINE ~ or SET SCAN OFF – Aitor Aug 18 '11 at 08:56
  • 1
    Nice solution and more pleasant than fiddling with global settings. A little shorter: 'Sid &' || ' Nancy' . – Dr. Hans-Peter Störr Jul 25 '12 at 13:48
5

the concat worked perfectly fine for me below is what i did in my select statement:

select FUND_NM
FROM PERFORMANCE
WHERE upper(FUND_DESC) in ('My L&' ||'L FUNDS')
andrewsi
  • 10,807
  • 132
  • 35
  • 51
NSC
  • 51
  • 1
  • 1
4

I use chr(38) where I need an ampersand in PL/SQL.

addr:= 'mysite.com/order.aspx?no=5678' || CHR(38) || 'id=947';
--above line gives you 'mysite.com/order.aspx?no=5678&id=947';
mason
  • 31,774
  • 10
  • 77
  • 121
2

This is just a SQL Editor issue. To resolve this just compile the procedure with SET DEFINE OFF; . Execution which is PL (executing in server) will not face this issue.

Saurabh Srivastava
  • 29
  • 1