SQL Plus - Substitution Variables

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

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.

SQLPLUS - Verify

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

Documentation / Reference


Powered by ComboStrap