3

I have a query

select * from table where name in ('52 T&M', '60 T&M');

The "&" is causing the query to expect a parameter. How do I qualify the "&" in the query to sting so that the query can find string with the "&" character in them?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
MikeTWebb
  • 9,149
  • 25
  • 93
  • 132

3 Answers3

9

The ampersand ("&") is a character interpreted by SQLPlus as a variable placeholder. Use:

SET DEFINE OFF
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • @OMG...whereabouts in the Procedure would that code go? As I mention below, the query is in a CURSOR degined before the BEGIN in the procedure – MikeTWebb Sep 08 '10 at 16:12
  • @MikeTWebb: At the top of the script. I wager this is an anonymous PLSQL block... – OMG Ponies Sep 08 '10 at 16:17
  • +1 I learned something new today! @MikeTWebb It would go as the first line of the script or there abouts. Whether the script is defining a procedure, package body or function via `CREATE OR REPLACE ....` or the script contains an anonymous PL/SQL block. – Shannon Severance Sep 08 '10 at 22:35
  • I knew about using ampersands to define prompts. I didn't know how to turn them off. I had been using string concatenation as a work around. (`'Foo&'||'Bar'` for `'Foo&Bar'`) – Shannon Severance Sep 09 '10 at 15:52
4

I would normally use set define off as suggested by omg but it is also possible to do it like this:

select *
from table
where name in ('52 T'||Chr(38)||'M', '60 T'||Chr(38)||'M');
Adam Butler
  • 3,023
  • 5
  • 35
  • 40
0

I assume you use sqlplus, so execute before

set scan off

Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
  • @Michael....the query string is located in the definition of a CURSOR. I tried your suggestion, but suspect the CURSOR definition is messing it up – MikeTWebb Sep 08 '10 at 16:11