How do I ignore ampersands in a SQL script running from SQL Plus?

user_20625 0
Viewed 198048

I have a SQL script that creates a package with a comment containing an ampersand (&). When I run the script from SQL Plus, I am prompted to enter a substitute value for the string starting with &. How do I disable this feature so that SQL Plus ignores the ampersand?

sqlplus oracle sql
7 Answers

This may work for you:

set define off

Otherwise the ampersand needs to be at the end of a string, 

'StackOverflow &' || ' you'

EDIT: I was click-happy when saving... This was referenced from a blog.

Austin_Salonen_40680

According to this nice FAQ there are a couple solutions.

You might also be able to escape the ampersand with the backslash character \ if you can modify the comment.

user_193870

I had a CASE statement with WHEN column = 'sometext & more text' THEN ....

I replaced it with WHEN column = 'sometext ' || CHR(38) || ' more text' THEN ...

you could also use WHEN column LIKE 'sometext _ more text' THEN ...

(_ is the wildcard for a single character)

user_86859160
Related