Oracle SQL*Plus: The Definitive Guide

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 even more, if you need to, with some creative use of SQL.

Validating Input with ACCEPT

Oracle has been steadily improving the ACCEPT command over the last few releases of SQL*Plus. These improvements all center around the issue of validation, and make it much easier to prevent a user from entering bad data in response to a prompt.

Tip

The ACCEPT command options illustrated in this section 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.

Throughout most of this book, the ACCEPT commands have all been written pretty much 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 will simply take whatever string the user types in and assign it to the variable. If you need to go beyond this, ACCEPT allows you to specify a datatype, and will not accept input that doesn’t convert to the type you specify. ACCEPT also ...