Handling of special Oracle symbols '&'

The default "&" in SQL * Plus indicates the substitution variable. That is to say, as long as the symbol appears in the command, SQL * Plus requires you to enter the substitution value. This means that you cannot input a string containing this symbol into the database or assign it to a variable. For example, the string "SQL & plus" system will understand it as a string with the "SQL" header, it prompts you to enter the value of the substitution variable plus. If you enter ABC, the final string is converted to "sqlabc ".

Method 1: set define off
Set define off disables this function. "&" is used as a common character. In the preceding example, the final character is "SQL & plus"
Set define off disable the substitution Variable Function
Set define on enable the variable substitution function
Set define * indicates the default substitution variable identifier as "*" (you can also set it to another character)
Method 2: replace '&' with CHR (38) in an SQL statement because CHR (38) is the ASCII code '&'.
SQL> select 'Tom '| CHR (38) | 'Jerry' from dual;
Method 3: Split the original string
SQL> select 'Tom '|' & '| 'Jerry' from dual;