1-11. Changing the Substitution Variable Character
Problem
You are interested in changing the substitution variable from & to some other character.
Solution
Issue the SET DEFINE command to set the new character. For example, say you want the substitution character to be a caret (^). To that end, you can issue the SET DEFINE command shown in the following example:
SQL> SET DEFINE ^
SQL> SELECT department_name
2 FROM departments
3 WHERE department_id = ^dept_id;
Enter value for dept_id: 150
old 3: where department_id = ^dept_id
new 3: where department_id = 150
DEPARTMENT_NAME
------------------------------
Shareholder Services
As shown in the example, the substitution variable dept_id is prefaced with the ^ symbol. That works, since the SET DEFINE command specifies that symbol as the one to use.
How It Works
Issue the SET DEFINE command when you want to change the substitution variable character recognized by SQL*Plus. The syntax for using the SET DEFINE command is as follows:
SET DEFINE character
The character can be any valid character. Any statement within the same SQL*Plus session will utilize that character to denote a substitution variable after this command is issued.
The solution in this recipe can be most useful if you are working with a piece of code that contains many occurrences of the default DEFINE character (&) in various string literals.