Datasheet

Accepting Values at Runtime 47
WHERE username is NOT NULL
ORDER BY logon_time;
USERNAME SID SERIAL# PROGRAM
-------------------- ---------- ---------- ---------------
DBSNMP 148 608 emagent.exe
DBSNMP 124 23310 emagent.exe
BTHOMAS 121 963 sqlplus.exe
SCOTT 132 23 TOAD.EXE
SJACOB 231 32 discoverer.exe
Accepting Values at Runtime
To create an interactive SQL statement, you can define variables in the SQL statement. This
allows the user to supply values at runtime, further enhancing the ability to reuse the SQL
scripts. An ampersand (
&) followed by a variable name prompts for and accepts values at
runtime. For example, the following
SELECT statement queries the DEPARTMENTS table based
on the department number supplied at runtime.
SELECT department_name
FROM departments
WHERE department_id = &dept;
Enter value for dept: 10
old 3: WHERE DEPARTMENT_ID = &dept
new 3: WHERE DEPARTMENT_ID = 10
DEPARTMENT_NAME
---------------
Administration
1 row selected.
Using Substitution Variables
Suppose that you have defined DEPT as a variable in your script, but you want to avoid the
prompt for the value at runtime. SQL*Plus prompts you for a value only when the variable
is undefined. You can define a substitution variable in SQL*Plus using the
DEFINE command
95127c01.indd 47 2/18/09 6:37:11 AM