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).
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.
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)
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.
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
or with prompt
PROMPT The value of MySubstitutionVariable is: &MySubstitutionVariable
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