120

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?

JoshL
  • 10,737
  • 11
  • 55
  • 61

7 Answers7

213

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
  • 49,173
  • 15
  • 109
  • 139
32

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 || '&' ||.

Leigh Riffel
  • 6,381
  • 3
  • 34
  • 47
  • 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 Dec 10 '10 at 21:47
  • 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. – Leigh Riffel Dec 13 '10 at 16:53
15

I resolved with the code below:

set escape on

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

Att

Cauca
  • 382
  • 3
  • 12
  • 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`. – mrswadge Feb 03 '15 at 09:41
9

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>
tvCa
  • 796
  • 6
  • 13
  • 1
    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. – Drumbeg Apr 01 '15 at 15:18
5

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.

Aleksandr M
  • 24,264
  • 12
  • 69
  • 143
ora
  • 51
  • 1
  • 1
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.

user19387
  • 223
  • 1
  • 2
  • 6
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)

AWOLKiwi
  • 19
  • 1
  • 3