How do I ignore ampersands in a SQL script running from SQL Plus?
SqlOracleSqlplusSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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 http://blog.andrewbeacock.com/2008/09/using-ampersands-without-variable_15.html">blog</a>;.
Solution 2 - Sql
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 || '&' ||.
Solution 3 - Sql
I resolved with the code below:
set escape on
and put a \ beside & in the left 'value_\&_intert'
Att
Solution 4 - Sql
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>
Solution 5 - Sql
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.
Solution 6 - Sql
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.
Solution 7 - Sql
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)