Sql*Plus User’s Guide and Reference

Example 5–13
Using PROMPT and ACCEPT in Conjunction with Substitution Variables
As you have seen in 
Example 5–12, "Prompting for and Accepting Input"
, SQL*Plus 
automatically generates a prompt for a value when you use a substitution variable. 
You can replace this prompt by including PROMPT and ACCEPT in the script with 
the query that references the substitution variable. First clear the buffer with:
CLEAR BUFFER
To create such a file, enter the following:
INPUT
PROMPT Enter a valid employee ID
PROMPT For Example 145, 206
ACCEPT ENUMBER NUMBER PROMPT 'Employee ID. :'
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE EMPLOYEE_ID=&ENUMBER;
Save this file as PROMPT2. Next, run this script. SQL*Plus prompts for the value of 
ENUMBER using the text you specified with PROMPT and ACCEPT:
START PROMPT2
SQL*Plus prompts you to enter an Employee ID:
205
What would happen if you typed characters instead of numbers? Since you specified 
NUMBER after the variable name in the ACCEPT command, SQL*Plus will not accept 
a non-numeric value:
Try entering characters instead of numbers to the prompt for "Employee ID.", 
SQL*Plus will respond with an error message and prompt you again to re-enter the 
correct number:
START PROMPT2
Enter a valid employee ID
For Example 145, 206
Employee ID. :
old 3: WHERE EMPLOYEE_ID=&ENUMBER
new 3: WHERE EMPLOYEE_ID= 205
Department Report
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Shelley Higgins 12000

---

Using Bind Variables
Using Scripts in SQL*Plus
5-21
When SQL*Plus prompts you to enter an Employee ID, enter the word "one" instead of 
a number:
one