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

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