O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required