Using ampersands (&) without variable substitution in Oracle either direct or with SQL*Plus

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 level

To 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*Plus

To 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 off
Add 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: , , , , ,

6 comments:

Anonymous said...

I already knew the Define Set Off.. but since i'm usign toad it never worked.. Thank you!! i did not knew about the string.. so obvious :D

Anonymous said...

You can also use:

SET SCAN OFF

to disable scanning for substitution variables (& and &&)

abeacock said...

Nice tip, thanks!

Anonymous said...

what if the string has more than one & ??

Glany said...

thanks

Anonymous said...

Hi There ,

I tried using Set define Off as well as set scan off but encountered the following error :

ORA-00922: missing or invalid option and still I am asked for input for &nbsp . I am using Toad for Oracle 10.5.3.1 , database being Oracle11g