Oracle uses the ampersand (&) symbol within a string to indicate a substitution variable. But what if you want to use an ampersand as part of a regular string such as 'Bob & Sons'?
There are two solutions that I know of and which one you use depends on the context in which you are running your SQL:
Dealing with the ampersand on an SQL levelTo turn off interpreting the ampersand as a substitution variable it must be at the end of a string:
insert into companies values ('Bob &' || ' Sons');
Dealing with the ampersand when using SQL*PlusTo disable Oracle's variable substitution and therefore return & to the pool of standard characters you need to tell SQL*Plus to disable it:
set define offAdd that to the top of your Oracle SQL script or type it manually at the SQL*Plus prompt.
These tips plus many more can be found within the rather useful The Oracle (tm) Users' Co-Operative FAQ.
Technorati Tags: Oracle, Database, Ampersand, Variable Substitution, SQLPlus, Andrew Beacock