I had a table in Oracle database that contains id, name fields.
It's data is:
123,'South'
121,'East'
445,'Africa & World'
When I select rows using where clause like
select * from where name = 'Africa & World';
Oracle Asks for a Substitution Variable and no records are returned.
Kindly tell me to further clarify my question.
Regards
The problem is that & is reserved as a marker for binding variables. You need to escape the & symbol.
You can escape the ampersand by
SET ESCAPE ON;
select * from where name = 'Africa \& World';
or you can set the bindings off by
SET DEFINE OFF;
select * from where name = 'Africa & World';
From http://www.orafaq.com/wiki/SQL_FAQ
Option #1
SET DEFINE ~
SELECT 'Laurel & Hardy' FROM dual;
Option #2
SET ESCAPE '\'
SELECT '\&abc' FROM dual;
Options #3
SET SCAN OFF
SELECT '&ABC' x FROM dual;
Option #4
SELECT 'Laurel ' || '&' || ' Hardy' FROM dual;
Option #5 (Use the 10g Quoting mechanism)
SELECT q'{This is Orafaq's 'quoted' text field}' FROM DUAL;
SET SCAN OFFThis is a SQL*Plus thing.
At the SQL*Plus prompt, type:
SET DEFINE OFF
Then try re-running your statement.