Sql*Plus User’s Guide and Reference

Using Predefined Variables
There are eight variables containing SQL*Plus information that are defined during 
SQL*Plus installation. These variables can be redefined, referenced or removed the 
same as any other variable. They are always available from session to session unless 
you explicitly remove or redefine them.
Using Substitution Variables
Suppose you want to write a query like the one in SALES (see 
Example 5–1, "Using a 
System Editor to Write a SQL Script"
) to list the employees with various jobs, not just 
those whose job is SA_MAN. You could do that by editing a different value into the 
WHERE clause each time you run the command, but there is an easier way.
By using a substitution variable in place of the text, SA_MAN, in the WHERE clause, 
you can get the same results you would get if you had written the values into the 
command itself.
A substitution variable is preceded by one or two ampersands (&). When SQL*Plus 
encounters a substitution variable in a command, SQL*Plus executes the command as 
though it contained the value of the substitution variable, rather than the variable 
itself.
For example, if the variable SORTCOL has the value JOB_ID and the variable 
MYTABLE has the value EMP_DETAILS_VIEW, SQL*Plus executes the commands
SELECT &SORTCOL, SALARY
FROM &MYTABLE
WHERE SALARY>12000;
as if they were
SELECT JOB_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
Where and How to Use Substitution Variables
You can use substitution variables anywhere in SQL and SQL*Plus commands, except 
as the first word entered. When SQL*Plus encounters an undefined substitution 
variable in a command, SQL*Plus prompts you for the value.
You can enter any string at the prompt, even one containing blanks and punctuation. 
If the SQL command containing the reference should have quote marks around the 
variable and you do not include them there, the user must include the quotes when 
prompted.
See Also:
Predefined Variables
 on page 12-45 for a list of the predefined 
variables and examples of their use. 

---

Using Substitution Variables
Using Scripts in SQL*Plus
5-13
SQL*Plus reads your response from the keyboard, even if you have redirected 
terminal input or output to a file. If a terminal is not available (if, for example, you run 
the script in batch mode), SQL*Plus uses the redirected file.
After you enter a value at the prompt, SQL*Plus lists the line containing the 
substitution variable twice: once before substituting the value you enter and once after 
substitution. You can suppress this listing by setting the SET command variable 
VERIFY to OFF.