Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Validating and Parsing User Input

Whenever you ask a user for input, you run the risk that it won't make sense. Maybe you are asking for a number, and the user types in some letters. Maybe you are asking for a date, and the user enters a bad value for the month. The SQL*Plus ACCEPT command offers some support for dealing with these situations. You can do more, if you need to, with some creative use of SQL.

Validating Input with ACCEPT

The ACCEPT command implements several options to help you validate user input. Throughout most of this book, the ACCEPT commands have been mostly written like this:

ACCEPT my_variable PROMPT 'Enter a value >'

This is a least-common-denominator version of the ACCEPT command that should work with any release of SQL*Plus. It takes whatever string the user types in and assigns it to the variable. If you need to go beyond this, ACCEPT allows you to specify a datatype and does not accept input that doesn't convert to the type you specify. ACCEPT also allows you to specify a format string that the input data must match. You can take good advantage of these options to make your scripts more bulletproof.

Tip

The ACCEPT command options illustrated in the following subsections apply to SQL*Plus Versions 8.0.3 and above. Not all options will be available under previous releases. The ACCEPT command is one that has changed a lot over the years. Check the documentation for the release you are using to see which options are available to you.

ACCEPTing numeric values

If you ...