Lesson 14	Prompting for input
Objective	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 diagram explains the syntax for these two commands:

Components of PROMPT in SQL*Plus

PROMPT: Displays a message to the user. May be abbreviated PRO.

message_text: The message that you want the PROMPT command to display.

ACCEPT: Prompts the user for a value, and accepts a response. May be abbreviated to ACC.

variable_name: Is the name of the substitution variable in which you want the user's response to be stored.

[NUMBER|CHAR|DATE]: Allows you to specify a datatype. NUMBER may be abbreviated to NUM.

FORMAT: Introduces a format string that is used to validate the user's input. May be abbreviated to FOR.

format_spec: 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.

DEFAULT: Introduces a default value, to be used if the user presses ENTER without responding to the prompt. May be abbreviated DEF

default_value: The value that you want to use as a default response.

PROMPT: Introduces the prompt text.

prompt_text: Is the text that you want SQL*Plus to display as a prompt.

NOPROMPT: Tells SQL*Plus not to display a prompt.

HIDE: Causes SQL*Plus not to echo the user's response back to the display. This is useful when prompting for a password.

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:

Syntax for PROMPT and ACCEPT

SQL Plus accept prompt

PROMPT commands: The PROMPT commands display a message reminding the user about what the script does.

ACCEPT user_name: The ACCEPT command allows the user to type in a username.

PROMPT: What user are you interested in? This is the prompt that the ACCEPT command uses.

&user_name: This text will be replaced by the username that is typed in when the script is run.

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.

Oracle Accept Commands | Prompting for input

Why should you keep your ACCEPT commands as simple as possible? There are two reasons.

One is that the ACCEPT command has changed a lot over the past few years. Each new release of SQL*Plus has included one or two new clauses that work with the ACCEPT command. If you are trying to write portable SQL*Plus scripts, you have to aim for the lowest common denominator. The PROMPT and HIDE clauses are recognizable by all versions of the software so you will be safe using them across versions. However, other clauses have been introduced more recently and will not work with all versions of the software.
A second reason why you should keep your ACCEPT commands simple is that the FORMAT and datatype clauses are not as useful as you might think. Regardless of what you specify for a datatype, the stored data is a text variable anyway. Even if you prompt for a date, it is the text representation of the date that is stored in the variable.

In addition, SQL*Plus does not handle complex formats very well, and it's possible to get into a situation where no input is acceptable. This is especially true when you use format strings containing commas, decimal points, and dollar signs. Take a look at this example:

SQL> ACCEPT some_number NUMBER FORMAT 09,999 PROMPT ">"
>23[[delete break]]

SP2-0598: "23" does not match input format "09,999"
>23,999[[delete break]]

SP2-0425: "23,999" is not a valid number

This example shows the limitations of the FORMAT and datatype clauses.
When you enter a valid number, it is rejected because it does not match the format. When you enter a number that matches the format, the comma prevents SQL*Plus from recognizing it as a number. You can not win with this one. There is nothing you can enter that SQL*Plus will accept. In spite of the problems, the FORMAT clause can still be useful You just have to keep it simple, and you need to test it out thoroughly to be sure that it will not put you in a situation like that shown here.

User Input Scripting - Quiz

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