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.

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 >

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

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:

1. ACCEPT input from the user.
2. Issue a COLUMN command to capture the value returned from the validation query.
3. Execute the validation query.
4. 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.