Substitution variables allow you to write generic SQL*Plus scripts. They let you mark places in a script where you want to substitute values at runtime.
A substitution variable is not like a true variable used in a programming language. Instead, substitution variables mark places in the text where SQL*Plus does the equivalent of search and replace at runtime, replacing the reference to a substitution variable with its value.
Substitution variables are set off in the text of a script by preceding them with one or two ampersand characters. Say, for example, you had this query to list all projects to which employee 107 had charged time:
SELECT DISTINCT p.project_id, p.project_name FROM project p INNER JOIN project_hours ph ON p.project_id = ph.project_id WHERE ph.employee_id = 107;
This query is specific to employee number 107. To run the query for a different employee, you would need to edit your script file, change the ID number, save the file, and then execute it. That’s a pain. You don’t want to do that. Instead, you can generalize the script by rewriting the SELECT statement with a substitution variable in place of the employee ID number. That script might look like this:
SELECT DISTINCT p.project_id, p.project_name FROM project p INNER JOIN project_hours ph ON p.project_id = ph.project_id WHERE ph.employee_id = &employee_id;
The ampersand in front of
&employee_id marks it as a variable. At runtime, when it reads the statement, ...