Sql*Plus User’s Guide and Reference

5-17
Restrictions 
You cannot use substitution variables in the buffer editing commands, APPEND, 
CHANGE, DEL, and INPUT, nor in other commands where substitution would be 
meaningless. The buffer editing commands, APPEND, CHANGE, and INPUT, treat 
text beginning with "&" or "&&" literally, like any other text string.
System Variables
The following system variables, specified with the SQL*Plus SET command, affect 
substitution variables:
See 
SET
 on page 12-88 for more information about system variables.
Passing Parameters through the START Command
You can bypass the prompts for values associated with substitution variables by 
passing values to parameters in a script through the START command.
You do this by placing an ampersand (&) followed by a numeral in the script in place 
of a substitution variable. Each time you run this script, START replaces each &1 in the 
file with the first value (called an argument) after START filename, then replaces each 
&2 with the second value, and so forth.
For example, you could include the following commands in a script called MYFILE:
SELECT * FROM EMP_DETAILS_VIEW
WHERE JOB_ID='&1'
AND SALARY='&2';
In the following START command, SQL*Plus would substitute PU_CLERK for &1 and 
3100 for &2 in the script MYFILE:
START MYFILE PU_CLERK 3100
When you use arguments with the START command, SQL*Plus DEFINEs each 
parameter in the script with the value of the appropriate argument.
System Variable
Affect on Substitution Variables
SET CONCAT
Defines the character that separates the name of a substitution 
variable or parameter from characters that immediately follow 
the variable or parameter—by default the period (.).
SET DEFINE
Defines the substitution character (by default the ampersand 
"&") and turns substitution on and off.
SET ESCAPE
Defines an escape character you can use before the substitution 
character. The escape character instructs SQL*Plus to treat the 
substitution character as an ordinary character rather than as a 
request for variable substitution. The default escape character is 
a backslash (\).
SET NUMFORMAT
Sets the default format for displaying numbers, including 
numeric substitution variables.
SET NUMWIDTH
Sets the default width for displaying numbers, including 
numeric substitution variables.
SET VERIFY ON
Lists each line of the script before and after substitution.

---

Passing Parameters through the START Command