Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from Oracle SQL*Plus: The Definitive Guide

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

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

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

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata