AMPERSAND in where clause mistaken for variable name

[dj...@att.com]

Hi everyone,

I have a query which is as follows

SELECT
column_1,
column_2
FROM
table
WHERE
column_3 = 'ABC&DEF';

The problem is 'ABC&DEF' represents a value in the table, but because of
the ampersand oracle mistakes it for a variable which has to be input
and prompts me for DEF.

My question : Is there a way to make oracle understand that 'ABC&DEF' is a
data value in the column and not prompt me for DEF.

Any input would be very much appreciated.

Thanks for your time and have a good day.

regds,
Jude

-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet

[Jurij Modic]

It is obvious you are having this problem in SQL*Plus. To avoid
SQL*Plus interpreting '&' as substitution character, you have several
options:

1. SET SCAN OFF
2. SET DEFINE OFF or SET DEFINE to any appropriate character other
than '&'
3. SET ESCAPE ON and then precede '&' with escape character (default
escape character is '\'

For more details look at SET command in SQL*Plus manual.

Regards,

Jurij Modic,
Ministry of Finance, Republic of Slovenia
----------------------------------------

[Kelly Young]

dj...@att.com wrote:
>
> Hi everyone,

>
> I have a query which is as follows
>
> SELECT
> column_1,
> column_2
> FROM
> table
> WHERE
> column_3 = 'ABC&DEF';
>
> The problem is 'ABC&DEF' represents a value in the table, but because of
> the ampersand oracle mistakes it for a variable which has to be input
> and prompts me for DEF.
>
> My question : Is there a way to make oracle understand that 'ABC&DEF' is a
> data value in the column and not prompt me for DEF.
>
> Any input would be very much appreciated.
>

> Thanks for your time and have a good day.
>
> regds,
> Jude
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

One workaround would be to issue the sqlplus command: set scan off

[David Richardson]

Jude,

Prior to the query enter the following:

set define ~

This will change the define symbol from & to ~ (tilde). You may use any
character that you want. This must be placed inside of your sql file
and will remain set until the seesion is over or you execute "set
define" again.

DER
--
David Richardson
2026 Aquia Drive
Stafford, VA 22554
1-800-425-6719
drich...@all-4-one.com
http://www.all-4-one.com