Prompting for Values
The most reliable and robust method for getting input from the user is to prompt for values using the ACCEPT and PROMPT commands. The ACCEPT command takes input from the user and stores it in a user variable and allows you some control over what the user enters. The PROMPT command may be used to display messages to the user , perhaps supplying a short summary of what your script is going to accomplish.
Several potential problems arise when you place substitution variables in your scripts and rely on SQL*Plus's default prompting mechanisms. All of these problems can be avoided through the use of the ACCEPT command. Table 8-1 provides a list of these problems, along with a description of how the ACCEPT and PROMPT commands can be used to overcome them.
Table 8-1. Potential problems with SQL*Plus's default prompting
|
Potential problem |
Solution |
|---|---|
|
Using double ampersands to define a variable in a script results in your not being prompted for a value the second time you run the script. |
Use the ACCEPT command to prompt for a value. This works regardless of whether the variable has previously been defined. |
|
Setting terminal output off, such as when spooling a report to a file, prevents you from seeing the prompts for substitution variables used in the query. |
Use the ACCEPT command to prompt for these values earlier in the script, before the SET TERMOUT OFF command is executed. |
|
The default prompt provided by SQL*Plus consists of little more than the variable name . |
Use the ACCEPT command to specify your own prompt. For longer explanations , the PROMPT command may be used. |
This section shows how to enhance the index listing script in Example 8-2 by using the PROMPT and ACCEPT commands. Use the PROMPT command to better explain what the script is doing and the ACCEPT command to prompt the user for the table name.
8.3.1 The ACCEPT Command
The ACCEPT command allows you to obtain input from the user. With it, you specify a user variable and text for a prompt. ACCEPT displays the prompt for the user, waits for the user to respond, and assigns the user's response to the variable.
|
You can make Example 8-2s script more reliable by using ACCEPT to get the table name from the user. This ensures that the user is prompted for a table name each time the script is run. The following ACCEPT command should do the trick:
ACCEPT table_name CHAR PROMPT 'Enter the table name >'
A good place to add the command would be prior to the COLUMN commands, resulting in the new script shown in Example 8-3.
Example 8-3. Using ACCEPT to receive user input
SET HEADING OFF
SET RECSEP OFF
SET NEWPAGE 1
ACCEPT table_name CHAR PROMPT 'Enter the table name >'
COLUMN index_name FORMAT A30 NEW_VALUE index_name_var NOPRINT
COLUMN uniqueness FORMAT A6 NEW_VALUE uniqueness_var NOPRINT
COLUMN tablespace_name FORMAT A30 NEW_VALUE tablespace_name_var NOPRINT
COLUMN column_name FORMAT A30
BREAK ON index_name SKIP PAGE on column_header NODUPLICATES
TTITLE uniqueness_var ' INDEX: ' index_name_var -
SKIP 1 ' TABLESPACE: ' tablespace_name_var -
SKIP 1
DESCRIBE &&table_name
SELECT ui.index_name,
ui.tablespace_name,
DECODE(ui.uniqueness,'UNIQUE','UNIQUE',' ') uniqueness,
' COLUMNS:' column_header,
uic.column_name
FROM user_indexes ui,
user_ind_columns uic
WHERE ui.index_name = uic.index_name
AND ui.table_name = UPPER('&table_name')
ORDER BY ui.index_name, uic.column_position;
TTITLE OFF
SET HEADING ON
SET RECSEP WRAPPED
CLEAR BREAKS
CLEAR COLUMNS
It doesn't really matter now whether the script uses &table_name or &&table_name for the substitution variable. Either will work well, and the script in Example 8-3 uses both. When you run the script, here's how the prompt will look:
SQL> @ex8-3 Enter the table name >
You can run this script many times in succession, and you will be prompted for a different table name each time. In addition, the prompt is a bit more user-friendly than the default prompt generated by SQL*Plus.
8.3.2 The PROMPT Command
The PROMPT command allows you to print text on the display for the user to read. PROMPT lets you provide informative descriptions of what a script is about to do. Use it to provide long and detailed prompts for information or add blank lines to the output to space things out a bit better. Any substitution variables in the prompt text are replaced by their respective values before the text is displayed.
|
8.3.2.1 Using PROMPT to summarize the script
It would be nice to add some messages to Example 8-3 to make the script more self-explanatory to the user. You can do that by adding the following PROMPT commands to the beginning of the script:
PROMPT PROMPT This script will first DESCRIBE a table. Then PROMPT it will list the definitions for all indexes PROMPT on that table. PROMPT
The first and last PROMPT commands space the output better by adding a blank line above and below the description.
8.3.2.2 Using PROMPT to explain the output
You can use the PROMPT command to explain the output of a script better. Appropriate messages can be added prior to the DESCRIBE command and the SELECT statement. The relevant part of the resulting script is shown in Example 8-4.
Example 8-4. Using PROMPT to better explain a script
... PROMPT PROMPT &table_name table definition: PROMPT DESCRIBE &&table_name PROMPT PROMPT Indexes defined on the &table_name table: PROMPT SELECT ui.index_name, ...
Following is the result of executing the script with all the PROMPT commands added. The messages not only make the output more clear, but they space it out better as well.
SQL>
@ex8-4
This script will first DESCRIBE a table. Then
it will list the definitions for all indexes
on that table.
Enter the table name >project_hours
project_hours table definition:
Name Null? Type
----------------------------------------- -------- ----------------
PROJECT_ID NOT NULL NUMBER(4)
EMPLOYEE_ID NOT NULL NUMBER
TIME_LOG_DATE NOT NULL DATE
HOURS_LOGGED NUMBER(5,2)
DOLLARS_CHARGED NUMBER(8,2)
Indexes defined on the project_hours table:
old 9: AND ui.table_name = UPPER('&table_name')
new 9: AND ui.table_name = UPPER('project_hours')
UNIQUE INDEX: PROJECT_HOURS_PK
TABLESPACE: USERS
COLUMNS: PROJECT_ID
EMPLOYEE_ID
TIME_LOG_DATE
