1-10. Ignoring Substitution Variables
Problem
You want to execute a script in SQL*Plus that contains elements that appear to be substitution variables, but you do not intend them to be substitution variables. You want the interpreter to ignore them instead of prompting the user for input.
Solution #1
One solution is to precede the & character with an escape character. The escape character tells SQL*Plus that what follows is not intended to be a variable reference.
In the following code, an escape character is used to tell SQL*Plus to ignore the & character when it is encountered and to treat “& Receiving” as simple text within a string:
SQL> SET ESCAPE ''
SQL> INSERT INTO DEPARTMENTS VALUES(
2 departments_seq.nextval,
3 'Shipping & Receiving',
4 null,
5 null);
1 row created.
Solution #2
Another solution is to completely disable the substitution variable feature. The next example uses the SET DEFINE OFF command to tell SQL*Plus that it should ignore all substitution variables:
SQL> SET DEFINE OFF
INSERT INTO DEPARTMENTS VALUES(
departments_seq.nextval,
'Importing & Exporting',
null,
null);
1 row created.
How It Works
Oftentimes you will encounter a situation where you need to tell SQL*Plus to ignore substitution variables for processing. As shown in the examples, there are a couple of different solutions in these cases. It is up to you to decide which method works best for you. Usually the method that is chosen depends upon the scenario.
Setting up an escape character via the SET ESCAPE command actually tells SQL*Plus to treat the designated character as the escape character for all scenarios, so whenever that character is encountered, then the character immediately following it should be ignored by the interpreter. By “ignored,” I mean that the character will not trigger the normal functionality that you would expect, such as prompting a user for input.
Using the SET DEFINE OFF method will cause all substitution variables to be ignored. In effect, this solution will affect only substitution variables and does not cause the interpreter to escape in any other scenario. Since this method only escapes substitution variables, it is better suited for use when running scripts. For instance, suppose you have a script named display_department_info.sql that contains the following SQL:
SELECT department_id
FROM departments
WHERE department_name = 'Importing & Exporting';
If you execute the script via SQL*Plus without using one of the solutions provided in this recipe, you will see the following message:
SQL> @display_department_id.sql
Enter value for exporting:
The reason this message occurs is because SQL*Plus is treating the ampersand in “Importing & Exporting” as a substitution variable, which prompts the user to enter text. Now, try executing the same script again, and this time issue the SET DEFINE OFF command first:
SQL> SET DEFINE OFF
SQL> @display_department_id.sql
DEPARTMENT_ID
-------------
360
Using SET DEFINE OFF gives you the expected results.