Substitution Variables in Oracle SQL*Plus

SQL*Plus allows you to use a kind of variable called a substitution variable, sometimes also called a user variable. A substitution variable is a marker you place in a script to mark a spot where you want to supply a value at the moment you actually run the script, rather than hardcoding a fixed value into the script itself. This lesson covers how to define them, how to have SQL*Plus prompt for them, and a few precise rules worth knowing before you rely on them in real scripts.

You define a substitution variable for repeated use in a script with the DEFINE command:

DEFINE variable_name = value

Note that DEFINE takes no trailing semicolon; it is a SQL*Plus command, not a SQL statement, and semicolons don't belong on this line. To define a substitution variable named L_NAME and give it the value SMITH, for example:

DEFINE L_NAME = SMITH

To confirm how a variable was actually defined, enter DEFINE followed by the variable name:

DEFINE L_NAME

which returns:

DEFINE L_NAME = "SMITH" (CHAR)

To list every substitution variable currently defined, enter DEFINE by itself with no arguments at all. One rule worth remembering: any substitution variable you define explicitly through DEFINE is always treated as a CHAR value, regardless of what the value looks like. If you need a NUMBER-typed variable, you get that implicitly through the ACCEPT command instead, covered below. To remove a substitution variable entirely, use UNDEFINE followed by the variable name.

Consider the database object report from earlier in this module:

SELECT owner,
       object_type,
       count(*) object_count,
       TO_CHAR(MAX(last_ddl_time),'dd-Mon-yyyy') last_ddl_time
FROM   dba_objects
GROUP BY owner, object_type
ORDER BY owner, object_type;

As written, this returns information for every schema in the database. Using a substitution variable, you can have SQL*Plus prompt for a specific owner name each time the script runs, and display only that owner's objects:

SELECT owner,
       object_type,
       count(*) object_count,
       TO_CHAR(MAX(last_ddl_time),'dd-Mon-yyyy') last_ddl_time
FROM   dba_objects
WHERE  owner = '&user_name.'
GROUP BY owner, object_type
ORDER BY owner, object_type;

Notice the &user_name. construct. The ampersand marks the beginning of a substitution variable; the period marks its end. Here, the variable name is user_name. When SQL*Plus encounters this in a script, it prompts using a standard "Enter a value for user_name:" message, waits for input, and substitutes whatever you type directly into the query before running it.

The trailing period is not strictly required if the variable name is followed by a space or some other punctuation; &user_name alone would have worked fine in this example too, and that's what you will see in most other people's scripts. The period only becomes necessary when a variable's substituted value would otherwise run directly into the next literal character with nothing to separate them, for instance when building a filename immediately after a variable. That terminator character is itself configurable, by the way: it defaults to a period through the SET CONCAT variable, so if you genuinely need a literal period immediately after a substituted value, you can change SET CONCAT to something else and use the period as ordinary text instead.

DEFINE sets a variable's value directly in the script itself; ACCEPT is what actually prompts the person running the script for input. Its full syntax is considerably richer than a simple prompt-and-store:

ACC[EPT] variable [NUMBER | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
       [FORMAT format] [DEFAULT default] [PROMPT text | NOPROMPT] [HIDE]

A basic example, prompting for a numeric employee ID:

ACCEPT v_id NUMBER PROMPT 'Please enter an employee ID: '
SELECT * FROM employees WHERE employee_id = &v_id;

Here, v_id is the variable name, NUMBER is the datatype, and the quoted text is the prompt message shown to whoever runs the script. If the reply doesn't match the specified datatype, ACCEPT gives an error and prompts again rather than accepting bad input silently. NUMBER, CHAR (up to 240 bytes), DATE, and the floating-point types BINARY_FLOAT and BINARY_DOUBLE are all valid datatype choices, each with its own validation behavior.

Beyond the datatype and PROMPT, three more clauses are worth knowing. FORMAT constrains the input to a specific format, re-prompting if what's typed doesn't match. DEFAULT supplies a value used automatically if the person running the script just presses Enter without typing anything. And NOPROMPT suppresses the prompt message entirely, useful when a script supplies its own explanatory text separately rather than relying on ACCEPT's built-in prompt.

One option genuinely deserves its own mention: HIDE. Adding HIDE to an ACCEPT command suppresses the typed value from echoing on screen at all, exactly the behavior you'd want when prompting for a password or any other value that shouldn't be visible to someone glancing at the screen while the script runs. This is a real, current SQL*Plus capability worth knowing the moment you write a script that needs to collect anything sensitive.

It's worth being precise about a distinction that's easy to blur: substitution variables and bind variables are two separate mechanisms, not two flavors of the same thing. A substitution variable, prefixed with an ampersand, is a SQL*Plus client-side text-replacement feature; SQL*Plus literally substitutes text into your command before sending it to the database at all. A bind variable, prefixed with a colon, belongs to SQL and PL/SQL directly:

VARIABLE v_id NUMBER
EXEC :v_id := 100;
SELECT * FROM employees WHERE employee_id = :v_id;

The two can pass values back and forth, assigning a substitution variable's value into a bind variable, or capturing a bind variable's value into a new substitution variable for later use in SQL*Plus commands, but neither is a category of the other. Keeping this distinction clear matters once you start combining the two in more advanced scripts.

So far, every substitution variable in this lesson has come from a user typing a response. You can also capture one directly from a query result, using the NEW_VALUE clause of the COLUMN command already covered earlier in this module. Here's a genuinely practical example, tying together COLUMN and SPOOL from previous lessons: building a report filename that includes today's date automatically.

COLUMN dcol NEW_VALUE mydate NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') dcol FROM dual;
SPOOL &mydate.report.txt
-- report content goes here
SPOOL OFF

The first query puts today's date into the substitution variable mydate. NOPRINT keeps that query's own output from cluttering the screen, since its only purpose is to populate the variable. In the SPOOL command, the period after mydate marks the end of the variable name, so it isn't included in the resulting filename itself. If mydate holds 20260827, the spool file ends up named 20260827report.txt. This same technique works for building any string you need at runtime, not just filenames.

A substitution variable can appear anywhere in a SQL or SQL*Plus command except as the very first word entered; SQL*Plus needs something else to establish context before it will resolve a variable reference. When SQL*Plus hits an undefined substitution variable, it prompts for a value, and you can type any string at that prompt, including one with blanks or punctuation. If the surrounding SQL command needs quote marks around the variable's value and you haven't included them in the script itself, you have to type the quotes yourself when prompted.

SQL*Plus reads your typed response from the keyboard even if you've redirected terminal input or output to a file elsewhere; the one exception is when no terminal is available at all, for instance when a script runs in true batch mode, in which case SQL*Plus falls back to reading from whatever input has been redirected. After you supply a value, SQL*Plus lists the line containing the substitution variable twice by default: once before the substitution and once after, so you can visually confirm the value landed where you expected. You can suppress this double listing by setting VERIFY to OFF:

SET VERIFY OFF

Between DEFINE for script-level values, ACCEPT for interactive prompting with real datatype validation and the HIDE option for sensitive input, and NEW_VALUE for capturing a value straight out of a query, substitution variables give you a genuinely flexible way to build scripts that adapt to whoever is running them, rather than hardcoding a single fixed scenario into every script you write. The next lesson builds on this directly, showing how the PROMPT command lets you generate more polished, user-friendly messages alongside the values you're collecting here.