Oracle and PL/SQL Recipes: A Problem-Solution Approach

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 ...