& IN WHERE CLAUSE of oracle

Question

I had SQL query running on SQL developer IDE:

SELECT IC.ID 
FROM INSTITUTE_COURSE IC 
WHERE IC.NAME = 'CIVIL & ARCHITECTURE CAD';

Due to & in where clause, ide returns an error "Enter Substitution Variable".

If I remove & in String, query runs fine.

My Question is how to run query with &/ampersand in where clause?

I tried a similar query (WHERE clause with a string contaning an &) into PL/SQL Developer and I didn't have this behavior. — Luc M, Sep 20 '11 at 13:13
Duplicate of: http://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus, & http://stackoverflow.com/questions/1137354/oracle-pl-sql-escape-character-for-a. — Shannon Severance, Sep 20 '11 at 18:45

score 7 · Accepted Answer · answered Sep 20 '11 at 12:59

7

Execute the command SET DEFINE OFF before executing your query.

answered Sep 20 '11 at 12:59

Dave Costa

2

actually this answer is better understood when the OP question has been tagged “sqlplus” – Benoit Sep 20 '11 at 13:26
@Benoit -- not sure what you mean. The command works in SQL Developer as well (I tested it before posting). – Dave Costa Sep 20 '11 at 15:05
SQL Developer is a tool that conforms to the SQL*Plus syntax as well for scripts to be portable. But '&' in a SQL query is correct if you program in C++ or use Tora or other oracle clients. – Benoit Sep 20 '11 at 15:53

score 4 · Answer 2 · answered Sep 20 '11 at 13:11

4

In addition to Dave Costa's answer, here are the relevant parts of the manual:

answered Sep 20 '11 at 13:11

Just FYI, these links are specifically about SQLPlus, but in this case the information applies to SQL Developer as well. – Dave Costa Sep 20 '11 at 13:22
@Dave: good point. But then the SQL Developer manuals simply mentions "SQL*Plus commands supported" but does not explain them, so apparently they work the same as in SQL*Plus – Sep 20 '11 at 13:44

2 Answers2