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

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

I resolved with the code below:

set escape on

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

Att

Answer 4

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>