Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

11.4 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.

11.4.1 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.

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.

11.4.1.1 ACCEPTing numeric values

If you
are prompting the user for a number, the first and easiest thing to
do is to use the NUMBER keyword with the ACCEPT command:

ACCEPT my_variable NUMBER PROMPT 'Enter a number >' When NUMBER is specified, SQL*Plus will accept any input that can be
converted to a number. Instead, it will keep repeating the prompt
until the user gets it right:

SQL> ACCEPT my_variable NUMBER PROMPT 'Enter a number >'
Enter a number >two
"two" is not a valid number
Enter a number >2.2.2
"2.2.2" is not a valid number
Enter a number >

SQL*Plus accepts a null input as a valid number, so if the user presses Enter, a "0" is stored in the variable. Spaces, on the other hand, do not constitute numeric input. Using a FORMAT clause for a number prevents null input from being accepted.

You can gain more control over numeric input by taking advantage of
the ACCEPT command's FORMAT clause. With it, you can
specify a numeric format string, and ACCEPT accepts only input that
matches that format. Supposedly, any format string valid for use with
the COLUMN command is valid for use with the ACCEPT command. In
practice, though, "9,"
"0," and
"." are the most useful as input
format specifiers.

Use "9"s when you want to limit the
user to entering a certain number of digits:

SQL> ACCEPT my_variable NUMBER FORMAT 999 PROMPT 'Enter a number >'
Enter a number >1234
"1234" does not match input format "999"
Enter a number >123
SQL>

However, the user is not forced to enter the maximum number of digits
allowed by the format string. The user may enter fewer digits, so
long as the result is a valid number:

SQL> ACCEPT my_variable NUMBER FORMAT 999 PROMPT 'Enter a number >'
Enter a number >12
SQL>

One advantage of the FORMAT clause is the user can't
get away without entering something. He must enter a valid number,
even if it is zero:

SQL> ACCEPT my_variable NUMBER FORMAT 999 PROMPT 'Enter a number >'
Enter a number >
" does not match input format "999"
Enter a number >0
SQL>

If you want to allow a decimal value to be entered, then you must
include a decimal point in the format string. The user will be
limited to the number of decimal places you specify:

SQL> ACCEPT my_variable NUMBER FORMAT 999.99 PROMPT 'Enter a number >'
Enter a number >19.76
SQL> ACCEPT my_variable NUMBER FORMAT 999.99 PROMPT 'Enter a number >'
Enter a number >19.763
"19.763" does not match input format "999.99"
Enter a number >19.8
SQL>

You can use a leading zero in a format string to force the user to
enter a specific number of digits:

SQL> ACCEPT my_variable NUMBER FORMAT 099 PROMPT 'Enter a number >'
Enter a number >1
"1" does not match input format "099"
Enter a number >12
"12" does not match input format "099"
Enter a number >123
SQL>

However, you can't use the zero after the decimal
point to force the user to enter a specific number of decimal digits.
The user may always enter fewer digits after the decimal than you
specify in the format string. For example, the following statement
accepts an input with a single decimal digit even though two are
specified in the format string:

SQL> ACCEPT my_variable NUMBER FORMAT 099.90 PROMPT 'Enter a number >'
Enter a number >123.1
SQL>

Negative values are allowed, regardless of whether the format string
specifies a sign. The following example uses a format string of 999
but still accepts a negative value:

SQL> ACCEPT my_variable NUMBER FORMAT 999 PROMPT 'Enter a number >'
Enter a number >-123
SQL>

SQL*Plus allows you to use other characters with the FORMAT clause
(see the COLUMN command for a complete list), but they may not work
as you would expect, and some don't work at all. The
"S" character, for example,
indicates a leading sign, but rather than being an optional sign it
is mandatory, so users must enter positive numbers with a leading
"+." That behavior may make sense
based on a strict interpretation of the manual, but
it's unlikely to be what you want. In some older
releases of SQL*Plus, I've had trouble using the $
as a number format specifier.

11.4.1.2 ACCEPTing date values

You
can deal with date values in much the same way as numeric values. The
first thing to do is to tell SQL*Plus you want a date. Use
the
DATE keyword with the ACCEPT command
like this:

ACCEPT my_variable DATE PROMPT 'Give me a date >' The date format accepted by SQL*Plus depends on your NLS_DATE_FORMAT
setting. Often this is DD-MON-YY, but it could be something different
depending on how Oracle is configured at your site. When the DATE
option is specified, ACCEPT rejects any input that
doesn't evaluate to a valid date:

SQL> ACCEPT my_variable DATE PROMPT 'Give me a date >'
Give me a date >11/15/61
"11/15/61" does not match input format "DD-MON-YY"
Give me a date >November 15, 1961
"November 15, 1961" does not match input format "DD-MON-YY"
Give me a date >15-Nov-61
SQL>

If you enter an invalid date, ACCEPT shows you the format
it's expecting. As with numbers, you can specify a
format string for dates. Any format string you can use with
Oracle's TO_DATE function may be used with the
ACCEPT command. Here are a couple of typical examples:

SQL> ACCEPT my_variable DATE FORMAT 'MM/DD/YY' PROMPT 'Give me a date >'
Give me a date >15-Nov-1961
"15-Nov-1961" does not match input format "MM/DD/YY"
Give me a date >11/15/61
SQL> ACCEPT my_variable DATE FORMAT 'DD-MON-YYYY' PROMPT 'Give me a date >'
Give me a date >11/15/61
"11/15/61" does not match input format "DD-MON-YYYY"
Give me a date >15-Nov-1961
SQL>

Remember that the result of an ACCEPT command is still a character string. The user may enter a date, but it is stored as a character string and will need to be converted again when your script next references that substitution variable.

ACCEPT is somewhat liberal when it comes to checking the date a user
enters against the specified format. ACCEPT allows a two- or
four-digit year, regardless of what you specify in the format string.
ACCEPT isn't too picky about separators and allows
hyphens even if your format string specifies slashes. The following
examples illustrate this behavior:

SQL> ACCEPT my_variable DATE FORMAT 'DD-MON-YYYY' PROMPT 'Give me a date >'
Give me a date >15-Nov-61
SQL> ACCEPT my_variable DATE FORMAT 'MM/DD/YY' PROMPT 'Give me a date >'
Give me a date >11-15-1961 Time of day is not treated with much respect by ACCEPT. You may ask
for it in your format string, but ACCEPT will take it or leave it. As
long as the user enters a date, ACCEPT doesn't care
about the rest:

SQL> ACCEPT my_variable DATE FORMAT 'MM/DD/YYYY HH:MI AM' PROMPT 'Give me
a date>'
Give me a date >11/15/1961
SQL>

The user input in response to an ACCEPT command is placed into a
substitution variable and those substitution variables are always
text. This is true with numbers and is true with dates. Look at the
following example:

SQL> ACCEPT my_variable DATE FORMAT 'MM/DD/YY' PROMPT 'Give me a date >'
Give me a date >7/4/98
SQL> DEFINE my_variable
DEFINE MY_VARIABLE = "7/4/98" (CHAR) The date entered was July 4, 1998. It is stored as the character
string "7/4/98," which matches the
input format used with the ACCEPT command. To reference the date
later in your script, you must use the TO_DATE function to convert it
again, and you must use the same format string you used to ACCEPT the
date. Failure to do this can result in the date's
being misinterpreted. The following SELECT, for example, interprets
the date entered in the preceding example using the European
convention of having the day first, followed by the month and year:

SQL> select to_date('&&my_variable','dd/mm/yyyy') from dual;
old 1: select to_date('&&my_variable','dd/mm/yyyy') from dual
new 1: select to_date('7/4/98','dd/mm/yyyy') from dual
TO_DATE('
---------
07-APR-98
SQL>

Suddenly, July 4, 1998 has become April 7, 1998. I
can't imagine wanting that type of behavior in a
script. To avoid problems, use the same date format consistently
every time you reference a substitution variable, whether
it's in an ACCEPT command or somewhere else in your
script.

11.4.2 Validating Input with SQL

The validation you get with the ACCEPT command is
limited. You can do more, if you need to, with the creative use of
SQL (or PL/SQL) together with the branching techniques discussed
earlier in this chapter. With a little thought and effort, you can:

Code more specific validations than you get with ACCEPT.

Accept more complicated input from the user.

You can, for example, write a script that asks the user for a date
and that requires all four digits of the year to be entered. You can
write a script that accepts several values in one string and then
pulls apart that string to get at each value. An example of this
would be allowing the user to specify a table using the standard
owner.tablename dot
notation syntax and defaulting the owner to the currently logged-on
user.

If you are going to code a complex edit check using SQL*Plus, you
need to do the following:

Decide whether the user's input is valid.

Take different actions depending on the result of that decision.

The first thing you need to decide is which branching technique you
are going to use because that tends to drive how you structure the
query you use for validation. Usually, if I'm in
this deep, I will branch using a multilevel file structure. To
facilitate this, I'll write the validation query to
return all or part of the filename to run next. If the input is bad,
the next script file will display an error message and quit.

The second thing to do is to write the SQL query to perform the
validation. Implementing the validation requires four steps:

ACCEPT input from the user.

Issue a COLUMN command to capture the value returned from the
validation query.

Execute the validation query.

Execute the script file returned by the query, which you captured
with the COLUMN command.

The short script in Example 11-29 illustrates how SQL
can be used to validate input by determining whether a date was
entered using a four-digit year, a two-digit year, or some other
quantity of digits.

Example 11-29. Using a SELECT and multilevel file structure to validate input

SET ECHO OFF
--Get a date from the user
ACCEPT start_date DATE FORMAT 'DD-MON-YYYY' PROMPT 'Start Date >'
--Get the next file to run, based on whether the date
--has a four-digit or a two-digit year.
SET TERMOUT OFF
COLUMN next_script_file NEW_VALUE next_script_file
SELECT DECODE (LENGTH(SUBSTR('&&start_date',
INSTR(TRANSLATE('&&start_date','/','-'),'-',-1)+1,
LENGTH('&&start_date')-
INSTR(TRANSLATE('&&start_date','/','-'),'-',-1))),
4,'ex11-29_four.sql &&start_date',
2,'ex11-29_two.sql &&start_date',
'ex11-29_bad.sql') next_script_file
FROM dual;
SET TERMOUT ON
--Execute the appropriate script
@&&next_script_file The three scripts referenced by Example 11-29 are
shown next:

oracle@gennick02:~/sqlplus/ExampleScripts> cat ex11-29_four.sql
PROMPT Four digit year: &1
oracle@gennick02:~/sqlplus/ExampleScripts> cat ex11-29_two.sql
PROMPT Two-digit year: &1
oracle@gennick02:~/sqlplus/ExampleScripts> cat ex11-29_bad.sql
PROMPT Year must be either two or four digits!
oracle@gennick02:~/sqlplus/ExampleScripts>

The following are several runs to test the functionality of Example 11-29:

SQL> @ex11-29
Start Date >15-Nov-1961
Four digit year: 15-Nov-1961
SQL> @ex11-29
Start Date >15-Nov-61
Two-digit year: 15-Nov-61
SQL> @ex11-29
Start Date >15-Nov-961
Year must be either two or four digits!

Admittedly, the DECODE expression in Example 11-29 is
complex, but it serves to illustrate how much you can accomplish with
Oracle's built-in functions.

11.4.3 Parsing Input with SQL

In addition to validating input, you can
use SQL and PL/SQL to parse it. Imagine for a moment that you are
writing a script to display information about the physical
implementation of a table. The script has to know which table you
want to look at, and one way to accomplish that is to pass the table
name as an argument like this:

@show_physical project_hours That's fine if you want to run the script on tables
you own. But what if you are the DBA and you want to examine tables
owned by other users? As with the DESCRIBE command, you may want to
allow for an optional owner name. Then you could also run the script
like this:

@show_physical jeff.project_hours The first problem you'll encounter in doing this is
that the argument jeff.project_hours is one string
and not two. The second problem is that you can't
depend on the owner to be always specified, and when
it's not specified you want it to default to the
currently logged-in user. One solution to these problems is to use
SQL to parse the input. One way to do that is to extend the WHERE
clauses of whatever queries are run by your script.
Here's a query to return the amount of space used by
a particular table:

SELECT SUM(bytes)
FROM dba_extents
WHERE segment_name = DECODE(INSTR('&&1','.'),
0,UPPER('&&1'),
UPPER(SUBSTR('&&1',INSTR('&&1','.')+1)))
AND owner = DECODE(INSTR('&&1','.'),
0,USER,
UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1)));

This solution works, but it can be cumbersome and error-prone because
the parsing logic has to be replicated in each query your script
executes. A better solution is to write some SQL at the beginning of
your script specifically to parse the input. That way, you end up
with two distinct substitution variables, one for the owner and one
for the table name, to use in the rest of your script. To do this
requires two steps. First, set up some COLUMN commands with NEW_VALUE
clauses. You need one of these COLUMN commands for each distinct
value in your input string. In keeping with the
owner.tablename example, the following two
commands could be used:

COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_name Second, you need to execute a query that returns the results you
want. In this case, the query needs to return the owner name and
table name as separate columns. Be sure to use column aliases to name
these columns, and be sure those aliases match the names used in the
COLUMN commands. The following SELECT takes a string in the form
owner.tablename and returns two separate
values. If the owner is not specified, the name of the current user
is returned instead:

SELECT
DECODE(INSTR('&&1','.'),
0,USER, /*Default to current user.*/
UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
DECODE(INSTR('&&1','.'),
0,UPPER('&&1'), /*Only the table name was passed in.*/
UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name
FROM dual;

Once the query has been executed, the substitution variables named in
the COLUMN commands will hold the values returned by the SELECT.
These substitution variables may be used in the remainder of the
script. The following is a rewrite of the previous SELECT using these
variables:

SELECT SUM(bytes)
FROM dba_extents
WHERE segment_name '&&s_table_name'
AND owner = '&&s_owner_name';

By using this technique, you have one point of change that controls
how the input is parsed. If there's a bug in your
logic, you need to fix it in only one place. The readability of your
script is greatly increased, too. You and others will understand your
scripts more clearly.

Example 10-14 implements the parsing technique shown in this section. Following that example is a detailed breakdown of the DECODE function that separates owner and table name.