124

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?

Improve this question

7 Answers 7

Reset to default
220

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

You can also specify this in the glogin.sql site profile setup file or the login.sql user profile setup file
This is the simplest solution if you aren't interested in substitution variables.
33

If you sometimes use substitution variables you might not want to turn define off. In these cases you could convert the ampersand from its numeric equivalent as in || Chr(38) || or append it as a single character as in || '&' ||.

Improve this answer

2 Comments

The specific scenario is a package whose source includes an ampersand in a comment. I don't see how I would use concatenation or substitution for this.
JoshL, you are correct I just listed this for completeness. It is related to your question even though it doesn't directly answer your specific question.
18

I resolved with the code below:

set escape on

and put a \ beside & in the left 'value_\&_intert'

Att

Improve this answer

2 Comments

This worked for me. I was using the command comment on column tablename.columnname is 'war ' || chr(38) || ' peace' but it was giving me the error ORA-01780: string literal required.
Good solution if you still need to use & variables in the scripts. +1
11

You can set the special character, which is looked for upon execution of a script, to another value by means of using the SET DEFINE <1_CHARACTER>

By default, the DEFINE function itself is on, and it is set to &

It can be turned off - as mentioned already - but it can be avoided as well by means of setting it to a different value. Be very aware of what sign you set it to. In the below example, I've chose the # character, but that choice is just an example.

SQL> select '&var_ampersand #var_hash' from dual;
Enter value for var_ampersand: a value

'AVALUE#VAR_HASH'
-----------------
a value #var_hash

SQL> set define #
SQL> r
  1* select '&var_ampersand #var_hash' from dual
Enter value for var_hash: another value

'&VAR_AMPERSANDANOTHERVALUE'
----------------------------
&var_ampersand another value

SQL>
Improve this answer

1 Comment

I've used this approach myself recently. I like it as it doesn't require me to alter the contents of my PL/SQL packages.
6

set define off <- This is the best solution I found

I also tried...

set define }

I was able to insert several records containing ampersand characters '&' but I cannot use the '}' character into the text So I decided to use "set define off" and everything works as it should.

Improve this answer

Comments

3

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.

Improve this answer

2 Comments

The backslash escape does not work in SQL*Plus or SQLDeveloper
@JimTough It does after activating it with set escape on
0

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)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.