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   amp    When I run the script from SQL Plus  I am prompted to enter a substitute value for the string starting with  amp   How do I disable this feature so that SQL Plus ignores the ampersand

User · Accepted Answer

This may work for you   set define off   Otherwise the ampersand needs to be at the end of a string     StackOverflow  amp        you    EDIT   I was click-happy when saving     This was referenced from a blog

User · Answer

I had a CASE statement with WHEN column    sometext  amp  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 · Answer

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 · Answer

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  lt 1 CHARACTER gt   By default  the DEFINE function itself is on  and it is set to  amp   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 gt  select   amp var ampersand  var hash  from dual  Enter value for var ampersand  a value   AVALUE VAR HASH  ----------------- a value  var hash  SQL gt  set define   SQL gt  r   1  select   amp var ampersand  var hash  from dual Enter value for var hash  another value    amp VAR AMPERSANDANOTHERVALUE  ----------------------------  amp var ampersand another value  SQL gt

User · Answer

set define off       lt - This is the best solution I found  I also tried     set define            I was able to insert several records containing ampersand characters   amp   but I cannot use the     character into the text So I decided to use  set define off  and everything works as it should

User · Answer

I resolved with the code below   set escape on   and put a   beside  amp  in the left   value   amp  intert   Att

User · Answer

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      amp

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

Examples related to sql

Examples related to oracle

Examples related to sqlplus