How to Use Substitution Variables in SQL*Plus

Substitution variables let one script run with different values. A reference such as &origin is replaced by the variable's value before the statement is sent to the database, in SQL*Plus and SQLcl alike. DEFINE sets values, && keeps a value for the rest of the session, and COLUMN NEW_VALUE fills a variable from a query result.

Code for This Guide

The main examples are in the examples/tools folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

set define on                     -- substitution on (the default character is &)
define name = value
select ... where col = '&name';     -- replaced before the statement runs
select ... where col = &&name;      -- defined for the rest of the session if prompted
undefine name
set verify on | off                 -- show or hide the old and new lines

An undefined &name makes the tool prompt for a value.

Define and Substitute

Example:

set define on verify on
define origin = 'SYD'
select destination, distance_km from routes where origin = '&origin';

define route_no = 5
select count(*) as flights from flights where route_id = &&route_no;
undefine route_no

Output:

old:select destination, distance_km from routes where origin = '&origin'
new:select destination, distance_km from routes where origin = 'SYD'

DESTINATION       DISTANCE_KM
______________ ______________
AKL                      2159
DXB                     12043
SIN                      6294

old:select count(*) as flights from flights where route_id = &&route_no
new:select count(*) as flights from flights where route_id = 5

   FLIGHTS
__________
        90

With VERIFY on, the tool prints each statement before and after substitution. &origin becomes SYD inside the quotes, and &&route_no becomes 5 without quotes, since it is a number. UNDEFINE removes the variable again.

Fill a Variable from a Query

Example:

set define on verify off
-- capture a query result in a substitution variable
column busiest new_value busiest_origin noprint
select origin as busiest from routes
group  by origin order by count(*) desc fetch first 1 row only;

prompt Busiest origin: &busiest_origin
select count(*) as routes from routes where origin = '&busiest_origin';
undefine busiest_origin

Output:

Busiest origin: DXB

    ROUTES
----------
        20

COLUMN ... NEW_VALUE copies the last value of a column into a substitution variable, and NOPRINT hides the column. The busiest origin, DXB, is then used in a prompt and in the next query.

Things to Know

• Substitution is plain text replacement: quote text values yourself, and prefer bind variables for values in production scripts.
• SET DEFINE OFF stops & from being treated as a variable, which scripts with & in text need.
• ACCEPT name PROMPT 'text' asks for a value with your own prompt.

Related Guides

• How to Create Command Aliases in SQLcl
• How to Format Query Output in SQLcl (SET SQLFORMAT)

Conclusion

Substitution variables make SQL*Plus and SQLcl scripts reusable: DEFINE values, reference them with & or &&, capture query results with COLUMN NEW_VALUE, and turn substitution off with SET DEFINE OFF when & is just text.