# SQL Plus - Substitution Variables

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 (&).

## 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

[email protected]>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```
```