SQL*Plus Tutorial

Substitution variables

Substitution variables are used to store temporary values.



For variables of type character or date is mandatory quoting.
Substitution variables can occur in a condition WHERE, in the ORDER BY clause, in the name of a table.



Syntax:

SELECT &column
FROM &table
WHERE &condition
ORDER BY &order ;

The variable remains seted until SQL * Plus session is closed or UNDEFINE command is given for the variable in question.

Example:

SQL > DEFINE name1 = Brad
SQL > DEFINE name2 = Stuart
SQL > SELECT first_name, age
2 FROM students
3 WHERE first_name = ' &name1 '
4 OR first_name = ' &name2 ';