SQL*Plus Substitution Variables

Substitution variables appear in SQL or SQL*Plus commands. SQL*Plus prompts for a value when you

execute those commands. We have used substitution variables in earlier examples in this book (Listing

5-14, for example, to test certain commands multiple times with different literal values.

Substitution variable values are volatile; that is, SQL*Plus doesn’t remember them and doesn’t store

them anywhere. This is what distinguishes substitution variables from the other two types. 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 (&), also known

as the DEFINE character. Check out what happens in Listing 11-1.

Listing 11-1. Using 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%')

 

  DEPTNO DNAME      LOCATION      MGR

-------- ---------- -------- --------

      10 ACCOUNTING NEW YORK     7782

      20 TRAINING   DALLAS       7566

      30 SALES      CHICAGO      7698

 

SQL>

 

288  CHAPTER 11 „ WRITING AND AUTOMATING SQL*PLUS SCRIPTS

Actually, if a substitution variable occurs twice within a single command, SQL*Plus also prompts

twice for a value, as demonstrated in Listing 11-2.

Listing 11-2. Prompting Twice for the Same Variable

SQL> select ename from employees

  2  where  empno between &x and &x+100;

 

Enter value for x: 7500

Enter value for x: 7500

old   2: where  empno between &x and &x+100

new   2: where  empno between 7500 and 7500+100

 

ENAME

--------

WARD

JONES

 

SQL>

 

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

shown in Listing 11-3. The period (.) is also known as the CONCAT character in SQL*Plus.

Normally, you don’t need the CONCAT character very often, because white space is good enough to

delimit variable names; however, white space in strings can sometimes be undesirable. See Listing 11-3

for an example.

Listing 11-3. Using the DEFINE and CONCAT Characters

SQL> select '&drink.glass' as result from dual;

 

Enter value for drink: beer

old   1: select '&drink.glass' as result from dual

new   1: select 'beerglass' as result from dual

 

RESULT

---------

beerglass

 

SQL>

 

Note that you can display the current settings of the DEFINE and CONCAT characters with the SQL*Plus

SHOW command, and you can change these settings with the SQL*Plus SET command, as shown in Listing

11-4. 

 

289 CHAPTER 11 „ WRITING AND AUTOMATING SQL*PLUS SCRIPTS

Listing 11-4. Displaying the DEFINE and CONCAT Character Settings

SQL> show define

define "&" (hex 26)

 

SQL> show concat

concat "." (hex 2e)

 

SQL>

 

If you don’t want SQL*Plus to display the explicit replacement of substitution variables by the values

you entered (as in Listings 11-1, 11-2, and 11-3), you can suppress that with the SQL*Plus VERIFY setting,

as shown in Listing 11-5.

Listing 11-5. Switching the VERIFY Setting ON and OFF

SQL> set  verify on

SQL> set  verify off

SQL> show verify

verify OFF

 

SQL>

 

If you change the VERIFY setting to OFF, as shown in Listing 11-5, and you execute the SQL command

(still in the SQL buffer) with the SQL*Plus RUN command, you don’t see the “old: ...” and “new: ...”

lines anymore, as shown in Listing 11-6.

Listing 11-6. The Effect of VERIFY OFF

SQL> select ename from employees

  2  where  empno between &x and &x+100;

 

Enter value for x: 7500

Enter value for x: 7500

 

ENAME

--------

WARD

JONES

 

SQL>