SQL* Plus CLI  «Prev  Next»

Lesson 14Prompting for input
ObjectiveUse 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:

  1. Displays a message to the user. May be abbreviated PRO.
  2. The message that you want the PROMPT command to display.
  3. Prompts the user for a value, and accepts a response. May be abbreviated to ACC.
  4. Is the name of the substitution variable in which you want the user's response to be stored.
  5. Allows you to specify a datatype. NUMBER may be abbreviated to NUM.
  6. Introduces a format string that is used to validate the user's input. May be abbreviated to FOR.
  7. Is a format string that is used to validate the user's input. This format string is built just like the one used for the COLUMN command.
  8. Introduces a default value, to be used if the user presses ENTER without responding to the prompt. May be abbreviated DEF.
  9. The value that you want to use as a default response.
  10. Introduces the prompt text.
  11. Is the text that you want SQL*Plus to display as a prompt.
  12. Tells SQL*Plus not to display a prompt.
  13. Causes SQL*Plus not to echo the user's response back to the display. This is useful when prompting for a password.
Using Descriptive Prompt
Although the syntax allows for several options, it's best to write your ACCEPT commands

as simply as possible.
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:

  1. The PROMPT commands display a message reminding the user about what the script does.
  2. The ACCEPT command allows the user to type in a username.
  3. What user are you interested in? This is the prompt that the ACCEPT command uses.
  4. This text will be replaced by the username that is typed in when the script is run.
Prompt Accept Syntax
When you run this script, the output will look like this:

SQL> @m5l9
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.

User Input Scripting - Quiz

Click the Quiz link below to test your knowledge of user input and scripting.
User Input Scripting - Quiz