Sql*Plus User’s Guide and Reference

Receiving a Substitution Variable Value 
Through PROMPT and ACCEPT, you can send messages to the end user and receive 
values from end-user input. PROMPT displays a message you specify on-screen to 
give directions or information to the user. ACCEPT prompts the user for a value and 
stores it in the substitution variable you specify. Use PROMPT in conjunction with 
ACCEPT when a prompt spans more than one line.
Example 5–12
Prompting for and Accepting Input
To direct the user to supply a report title and to store the input in the variable 
MYTITLE for use in a subsequent query, first clear the buffer:
CLEAR BUFFER
Next, set up a script as shown and save this file as PROMPT1:
PROMPT Enter a title of up to 30 characters
ACCEPT MYTITLE PROMPT 'Title: '
TTITLE LEFT MYTITLE SKIP 2
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN'
SAVE PROMPT1
The TTITLE command sets the top title for your report. See 
Defining Page and Report 
Titles and Dimensions
 on page 6-17 for more information about the TTITILE 
command.
Finally, run the script, responding to the prompt for the title as shown:
START PROMPT1
Before continuing, turn the TTITLE command off:
TTITLE OFF
Created file PROMPT1.sql
Enter a title of up to 30 characters
Title: Department Report
Department Report
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
145 John Russell 14000
146 Karen Partners 13500
147 Alberto Errazuriz 12000
148 Gerald Cambrault 11000
149 Eleni Zlotkey 10500

---

Communicating with the User
5-20
SQL*Plus User's Guide and Reference
Customizing Prompts for Substitution Variable 
If you want to customize the prompt for a substitution variable value, use PROMPT 
and ACCEPT in conjunction with the substitution variable, as shown in the following 
example.