Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Prompting for Values

The most reliable and robust method for getting input from the user is to prompt for values using the ACCEPT and PROMPT commands. The ACCEPT command takes input from the user and stores it in a user variable and allows you some control over what the user enters. The PROMPT command may be used to display messages to the user, perhaps supplying a short summary of what your script is going to accomplish.

Several potential problems arise when you place substitution variables in your scripts and rely on SQL*Plus's default prompting mechanisms. All of these problems can be avoided through the use of the ACCEPT command. Table 8-1 provides a list of these problems, along with a description of how the ACCEPT and PROMPT commands can be used to overcome them.

Table 8-1. Potential problems with SQL*Plus's default prompting

Potential problem	Solution
Using double ampersands to define a variable in a script results in your not being prompted for a value the second time you run the script.	Use the ACCEPT command to prompt for a value. This works regardless of whether the variable has previously been defined.
Setting terminal output off, such as when spooling a report to a file, prevents you from seeing the prompts for substitution variables used in the query.	Use the ACCEPT command to prompt for these values earlier in the script, before the SET TERMOUT OFF command is executed.
The default prompt provided by SQL*Plus consists of little more than the variable name.	Use ...