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>