SQL Plus - Substitution Variables

Card Puncher Data Processing


You can define variables, called substitution variables, for repeated use in a single script. Note that you can also define substitution variables to use in titles and to save your keystrokes (by defining a long string as the value for a variable with a short name).

A substitution variable is preceded by one or two ampersands (&).



When SQLPlus find a substitution variable define by using only one ampersand (&),

  • it tries to replace it with the value of one permanent substitution variable previously defined
  • otherwise, it will prompt you to enter a value that will be use only once.


A permanent Substitution Variables is a variable available for the complete session and is created with two ampersands (see below).


Set up a substitution Variable ?

To define a substitution variable, you can use:

  • the DEFINE command
  • two Ampersands
  • the ACCEPT command


The DEFINE Command:


Note that any substitution variable you define explicitly through DEFINE takes only CHAR values (that is, the value you assign to the variable is always treated as a CHAR datatype). You can define a substitution variable of datatype NUMBER implicitly through the ACCEPT command.

Two ampersands

SQL*Plus automatically apply the DEFINE command to any substitution variable preceded by two ampersands, but does not DEFINE those preceded by only one ampersand.

select &&MySubstitutionVariable from dual;
Enter value for mysubstitutionvariable: 'Value'
old   1: select &&MySubstitutionVariable from dual
new   1: select 'Value' from dual


gerardnico@orcl>DEFINE MySubstitutionVariable


The ACCEPT command permits to interact with the user through the console and to create variable from the user input.


You can store the value of a column value in a variable using the column command:

COLUMN column_name NEW_VALUE variable_name


column myAliasColumnName NEW_VALUE myVariable
select 'Nico is the best' myAliasColumnName from dual; 
define myVariable
DEFINE MYVARIABLE      = "Nico is the best" (CHAR)

How to delete a substitution variable?

To delete a substitution variable, use the SQL*Plus command UNDEFINE followed by the variable name.

UNDEFINE MySubstitutionVariable

How to list … ?

all substitution variables

To list all substitution variable definitions, enter DEFINE by itself.

DEFINE _DATE           = "14-JUN-10" (CHAR)
DEFINE _USER           = "DWH" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.
4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
DEFINE _O_RELEASE      = "1002000400" (CHAR)

One substitution variable

DEFINE MySubstitutionVariable

or with prompt

PROMPT The value of MySubstitutionVariable is: &MySubstitutionVariable


Configuration occurs with the following System Variables

Prefix substitution variables

SET DEFINE sets the character used to prefix substitution variables (by default the ampersand “&”) and turns substitution on and off.

set define '&'

Turn on or off substitution variables

set define off
set define on

Suppress the substitution variable control

After you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution.

SQLPLUS - Verify

--Lists each line of the script before and after substitution.
-- You can suppress this listing by setting the SET command variable VERIFY to OFF.

Documentation / Reference

Discover More
Card Puncher Data Processing
Oracle Apex - Substitution String

You can use substitution strings within: a page template or region source to replace a character string with another value. See Built-in...
Card Puncher Data Processing
SQL Plus - ACCEPT command (Console Interaction)

The accept command reads a line of input and stores it in a given substitution variable. where: variable is the variable command prompt is the prompt command HIDE will hide the input For...
Card Puncher Data Processing
SQL Plus - Argument

The arguments of a script become become substitution variable: &1 for the first one, &2 for the second one, etc .... SQLPLUS doesn't have any flow control (IF statement), you have to do them in...
Card Puncher Data Processing
SQL Plus - Column Command

The column command: specifies display attributes for a given column, such as: text for the column heading alignment for the column heading format for NUMBER data wrapping of column data lists...
Card Puncher Data Processing
SQL Plus - Concat

The Concat system variable sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQLPlus would otherwise interpret...
Card Puncher Data Processing

Define is: a command that permit to initialize a substitution variable of a system variable The define system variable sets the character used to prefix the substitution variables to c.
Card Puncher Data Processing
SQL Plus - Editor

The dynamic substitution variable _EDITOR gives the current editor You use it in conjunction with the edit function. You must define it in an startup script
Card Puncher Data Processing
SQL Plus - Special Characters

Useful command in sqlplus. The At & character is the default character that define a substitution variable. You can change it with the define system variable. The slash command (/): ...
Card Puncher Data Processing
SQL Plus - User

Where can if find user information in SQL Plus. The dynamic substitution variable _USER gives the current user Their is also a user system variable
Card Puncher Data Processing
SQL Plus - Variable

VARIABLE is a function that: declares a bind variable that can be referenced in SQL and PL/SQL lists the current display characteristics for a single variable or all variables. substitution variable...

Share this page:
Follow us:
Task Runner