About
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 (&).
Articles Related
Scope
Temporary
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.
Permanent
A permanent Substitution Variables is a variable available for the complete session and is created with two ampersands (see below).
Management
Set up a substitution Variable ?
To define a substitution variable, you can use:
- the DEFINE command
- two Ampersands
- the ACCEPT command
- the COLUMN NEW_VALUE
DEFINE
The DEFINE Command:
DEFINE L_NAME = SMITH
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
'VALUE'
-------
Value
gerardnico@orcl>DEFINE MySubstitutionVariable
DEFINE MYSUBSTITUTIONVARIABLE = "'Value'" (CHAR)
ACCEPT
The ACCEPT command permits to interact with the user through the console and to create variable from the user input.
COLUMN NEW_VALUE
You can store the value of a column value in a variable using the column command:
COLUMN column_name NEW_VALUE variable_name
Example:
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.
SQL> DEFINE
DEFINE _DATE = "14-JUN-10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "bidb" (CHAR)
DEFINE _USER = "DWH" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000300" (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" (
CHAR)
DEFINE _O_RELEASE = "1002000400" (CHAR)
DEFINE MY_SUBSTITUTION_VARIABLE = "1" (CHAR)
One substitution variable
DEFINE MySubstitutionVariable
or with prompt
PROMPT The value of MySubstitutionVariable is: &MySubstitutionVariable
Configuration
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.
--Lists each line of the script before and after substitution.
SET VERIFY ON
-- You can suppress this listing by setting the SET command variable VERIFY to OFF.
SET VERIFY OFF