SQL Plus - Substitution Variables

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

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

Advertising

3 - Scope

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

3.2 - Permanent

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

4 - Management

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

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

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

4.1.3 - ACCEPT

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

4.1.4 - 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)
Advertising

4.2 - How to delete a substitution variable?

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

UNDEFINE MySubstitutionVariable

4.3 - How to list ... ?

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

4.3.2 - One substitution variable

DEFINE MySubstitutionVariable

or with prompt

PROMPT The value of MySubstitutionVariable is: &MySubstitutionVariable

5 - Configuration

Configuration occurs with the following System Variables

5.1 - 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 '&'

5.2 - Turn on or off substitution variables

SET define off
SET define ON

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

6 - Documentation / Reference

lang/sqlplus/substitution_variable.txt · Last modified: 2017/05/09 16:51 by gerardnico