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.
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 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.
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. Here’s an example:
ACCEPT my_variable NUMBER PROMPT 'Enter a number >'
When NUMBER is specified, SQL*Plus won’t accept any input that can’t be converted to a number. Instead, it will keep repeating the prompt until the user gets it right; for example:
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 >
Tip
SQL*Plus will accept a null input as a valid number, so if the user just presses ENTER, a “0” will be stored in the variable. Spaces, on the other hand, do not constitute numeric input. Using a FORMAT clause for a number will prevent 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 will only accept input that matches that format. Supposedly, any format string valid for use with the COLUMN command is also valid for use with the ACCEPT command. In practice, though, the “9”, “0”, and “.” are the most useful as input formats.
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 >123SQL>
Note, though, that 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 >12SQL>
One advantage of the FORMAT clause is that the user cannot get away without entering something. A valid number, even if it’s zero, must be entered.
SQL>ACCEPT my_variable NUMBER FORMAT 999 PROMPT 'Enter a number >'Enter a number > "" does not match input format "999" Enter a number >0SQL>
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.76SQL>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.8SQL>
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 >123SQL>
However, you cannot 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.1SQL>
Negative values are always allowed, regardless of whether the format string specifies a sign or not. 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 >-123SQL>
SQL*Plus will allow 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.
Things get even stranger if you use the dollar sign as part of a format string. Take a look at the following interaction with the ACCEPT command using a format of $999:
SQL>ACCEPT my_variable NUMBER FORMAT $999 PROMPT 'Enter a number >'Enter a number >123"123" does not match input format "$999" Enter a number >$123"$123" is not a valid number
SQL*Plus seems to be in a Catch-22 situation here. SQL*Plus correctly recognizes that “123” does not have a leading dollar sign, and thus does not match the input format. On the other hand, when you enter “$123”, the value is not recognized as a number.
You can deal with date values in much the same way as numeric values. The first thing to do is tell SQL*Plus you want a date. Do this by using 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 will depend on your NLS_DATE_FORMAT setting. Often this will be 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 will reject any input that doesn’t evaluate to a valid date; for example:
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-61SQL>
You can see that if you enter an invalid date, ACCEPT will show you the format it’s expecting. As with numbers, you can also specify a format string for dates. Any format string you can use with Oracle’s TO_DATE function may also 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/61SQL>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-1961SQL>
Tip
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 will allow either a two- or four-digit year, regardless of what you specify in the format string. ACCEPT is also not too picky about separators, and will allow 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-61SQL>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 too much respect by ACCEPT either. 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; for example:
SQL>ACCEPT my_variable DATE FORMAT 'MM/DD/YYYY HH:MI:SS AM' PROMPT 'Give me a date >'Give me a date >11/15/1961SQL>
Bear in mind that the user input in response to an ACCEPT command is always placed into a substitution variable, and that substitution variables are always text. This is true with numbers, and is just as 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/98SQL> 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 could result in the date being misinterpreted. The following SELECT, for example, interprets the same date 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 instead become April 7, 1998! I can’t imagine ever 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.
The validation you get with the ACCEPT command is rather 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 could, 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 could also 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 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 be able to do two fundamental things:
Decide whether or not 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 no good, the next script file will simply display an error message and quit. The second thing to do is write the SQL query to perform the validation. Implementing the validation requires these 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 following short script example illustrates how SQL can be used to validate input by to determining whether or not a date was entered with a four-digit year:
--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.
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,'four_digit_year.sql &&start_date',
2,'two_digit_year.sql &&start_date',
'bad_date.sql') next_script_file
FROM dual;
--Execute the appropriate script
@&&next_script_fileAdmittedly, the DECODE expression is a bit complex, but it serves to illustrate how much you can accomplish with Oracle’s built-in functions.
In addition to simply validating input, you can also 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 always want to run the script on tables you own. But what if you are the DBA and 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, not two. The second problem is that you can’t depend on the owner always to be specified, and when it’s not specified, you want it to default to the currently logged-in user. The solution to these problems is to use SQL to parse the input. One way to do that is simply 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. There are two steps to doing this. 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.table 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
Next, 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.table, 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 now 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 only need to fix it in one place. The readability of your script is greatly increased, too. Your scripts will also be more clearly understood by others, and probably by yourself as well.