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:
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
When you run this script, the output will look like 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.
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 ">"
SP2-0598: "23" does not match input format "09,999"
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.