RelationalDBDesign RelationalDBDesign


SQL* Plus CLI  «Prev 

Oracle Accept Commands

Prompting for input

Keep ACCEPT simple

Why should you keep your ACCEPT commands as simple as possible? There are two reasons.
  1. 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.
  2. 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:

OCA Oracle Database
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.