Substitution variables appear in SQL or SQL*Plus commands.

SQL*Plus prompts for a value when you execute those commands.

Substitution variable values are volatile: SQL*Plus doesn't store them anywhere.

If you execute the same SQL or SQL*Plus command again, SQL*Plus prompts for a value again.

The default character that makes SQL*Plus prompt for a substitution variable value is the ampersand (&).

It is known as the DEFINE character.

SQL> select * from departments 2 where dname like upper('%&letter%'); Enter value for letter: a old 2: where dname like upper('%&letter%') new 2: where dname like upper('%a%') SQL>

If a substitution variable occurs twice within a single command, SQL*Plus also prompts twice for a value.

SQL> select ename from emp 2 where empno between &x and &x+100; Enter value for x: 1234 Enter value for x: 1234 old 2: where empno between &x and &x+100 new 2: where empno between 1234 and 1234+100 SQL>

You can use the period character (.) to mark the end of the name of a substitution variable.

The period (.) is known as the CONCAT character in SQL*Plus.

SQL> select '&drink.glass' as result from dual; Enter value for drink: ABCD old 1: select '&drink.glass' as result from dual new 1: select 'ABCDglass' as result from dual RESULT --------- ABCDglass SQL>

You can display the current settings of the DEFINE and CONCAT characters with the SQL*Plus SHOW command.

You can change these settings with the SQL*Plus SET command.

SQL> show define define "&" (hex 26) SQL> show concat concat "." (hex 2e) SQL>

To turn off displaying the substitution variables, you can suppress this display with the SQL*Plus VERIFY setting.

SQL> set verify on SQL> set verify off SQL> show verify verify OFF SQL>

The Effect of VERIFY OFF

SQL> select ename from emp 2 where empno between &x and &x+100; Enter value for x: 1234 Enter value for x: 1234 SQL>

- Define System Variable
- Commonly used SQL*Plus system variables
- Implicit SQL*Plus User-Defined Variables
- User-Friendly Prompting
- SQL*Plus System Variables
- SQL*Plus User-Defined Variables