Use the SQL*Plus ACCEPT and PROMPT commands to get input from the user.
Prompting For SQL*Plus Input(ACCEPT PROMPT commands)
You can design your own prompts using the ACCEPT and PROMPT commands. ACCEPT is used to get input from a user and allows you to specify a short prompt. The PROMPT command simply displays a message to the user and can be used to display extended explanatory information.
The following mouseover explains the syntax for these two commands:
Now that you know about PROMPT and ACCEPT, you could modify the previous lesson's script, and make it more user-friendly by having it display some explanatory text, and by using a more descriptive prompt. The following mouseover shows one way to do this:
This script displays a summary of objects owned
by a user, telling you how many the user has of
each type, and telling you how recently an object
of each type was modified.
What user are you interested in?SYSTEMold 6: WHERE owner = '&user_name.'
new 6: WHERE owner = 'SYSTEM'
In addition to a more user-friendly prompt, ACCEPT provides you with another benefit.
It prevents any value that a prior script may have stored in a user variable from being reused. ACCEPT ensures that you are always prompted for a variable. Just referencing a variable in your script, as in the previous lesson, does not guarantee that you will be prompted for it.