Oracle SQL*Plus: The Definitive Guide

Prompting for Values

The most reliable and robust method for getting input from the user is to explicitly 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 also allows you some level of 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.

There are several potential problems that arise when you simply 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 4.1 provides a list of these problems together with a description of how the ACCEPT and PROMPT commands can be used to overcome them.

Table 4-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 ...