Datasheet

Accepting Values at Runtime 49
The old line with the variable and the new line with the substitution are displayed. You
can turn off this display by using the command
SET VERIFY OFF.
Saving a Variable for a Session
Consider the following SQL, saved to a file named ex01.sql. When you execute this script
file, you will be prompted for the
COL1 and COL2 values multiple times:
SQL> SELECT &COL1, &COL2
2 FROM &TABLE
3 WHERE &COL1 = ‘&VAL’
4 ORDER BY &COL2
5
SQL> SAVE ex01
Created file ex01.sql
SQL> @ex01
Enter value for col1: FIRST_NAME
Enter value for col2: LAST_NAME
old 1: SELECT &COL1, &COL2
new 1: SELECT FIRST_NAME, LAST_NAME
Enter value for table: EMPLOYEES
old 2: FROM &TABLE
new 2: FROM EMPLOYEES
Enter value for col1: FIRST_NAME
Enter value for val: John
old 3: WHERE &COL1 = ‘&VAL’
new 3: WHERE FIRST_NAME = ‘John’
Enter value for col2: LAST_NAME
old 4: ORDER BY &COL2
new 4: ORDER BY LAST_NAME
FIRST_NAME LAST_NAME
-------------------- ---------
John Chen
John Russell
John Seo
3 rows selected.
SQL>
The user can enter different or wrong values for each prompt. To avoid multiple prompts,
use
&& (double ampersand), where the variable is saved for the session.
95127c01.indd 49 2/18/09 6:37:11 AM