Using Substitution Variables
Substitution variables allow you to write generic SQL*Plus scripts. They allow you to mark places in a script where you want to substitute values at runtime.
What Is a Substitution Variable?
A substitution variable is the same thing as a user variable. In the previous chapter, you saw how to get the contents of a database column into a user variable and how to place the contents of that user variable into the page header of a report. SQL*Plus also allows you to place user variables in your script to mark places where you want to supply a value at runtime. When you use them this way, they are called substitution variables.
A substitution variable is not like a true variable used in a programming language. Instead, a substitution variable marks places in the text where SQL*Plus does the equivalent of a 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 either one or two ampersand characters. Say, for example, that 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,
project_hours ph
WHERE ph.employee_id = 107
AND p.project_id = ph.project_id;As you can see, 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, then execute it. ...