Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Using Substitution Variables

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.

What Is a Substitution Variable?

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, ...