I had a query that I was trying to run - update work_order_control set description = 'Cust Sv-C&I-Upgrade- VA' where work_order_number = 'BAP0000024';
When running this query in TOAD (or sql plus, or sql-station, probably most others) I run into this issue where the tool or Oracle thinks the symbol after the & is a variable and asks for a value for that variable. What I wanted to know was is there an "override" character that is used to allow this to run without thinking there is a variable due to the & symbol instead of using it as the intended literal.
Toad will follow the same standard and functionality as other oracle tools. However, you can try these method below as a workaround.
Escape ampersand (&) characters in SQL*Plus
1. When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:
SET DEFINE ~
SELECT 'Lorel & Hardy' FROM dual;
2. Define an escape character:
SET ESCAPE '\'
SELECT '\&abc' FROM dual;
3. Don't scan for substitution variables:
SET SCAN OFF
SELECT '&ABC' x FROM dual;
