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
__________
90With 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
----------
20COLUMN ... 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
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.
See my articles more often when you search Google.
