Oracle SQL*Plus: The Definitive Guide

Prompting for Values

The most reliable and robust method for getting input from the user is to explicitly 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 also allows you some level of 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.

There are several potential problems that arise when you simply 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 4.1 provides a list of these problems together with a description of how the ACCEPT and PROMPT commands can be used to overcome them.

Table 4-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 LIST_INDEXES script with the PROMPT and ACCEPT commands. The PROMPT command will be used to better explain what the script is doing, while the ACCEPT command will be used to reliably prompt the user for the table name.

The ACCEPT Command

The ACCEPT command is used to obtain input from the user. With it, you specify a user variable and text for a prompt. The ACCEPT command displays the prompt for the user, waits for the user to respond, and assigns the user’s response to the variable.

Syntax for the ACCEPT command

Here is the syntax for the ACCEPT command:

ACC[EPT] user_variable [NUM[BER]|CHAR|DATE] 
       [FOR[MAT] format_specification] 
       [DEF[AULT] default_value] 
       [PROMPT prompt_text|NOPR[OMPT]] 
       [HIDE]

where:

ACC[EPT]

Tells SQL*Plus that you want to prompt the user for a value, and that you want the value stored in the specified user variable. The command may be abbreviated to ACC.

user_variable

Is the variable you want to define. Do not include leading ampersands. If your script uses a “&table_name” for a substitution variable, you should used “table_name” here.

NUMBER|CHAR|DATE

Is the type of data you are after. The default is CHAR, which allows the user to type in anything as a response. Use NUMBER to force the user to enter a number and DATE when you want a date.

FOR[MAT] format_specification

This is an optional format specification, which may optionally be enclosed in quotes. If this is specified, ACCEPT will reject any input that does not conform to the specification. An error message will be displayed, and the prompt reissued. Specifying a format makes the most sense when dealing with numeric and date data, and SQL*Plus is actually somewhat loose in enforcing the format. Chapter 7, delves into this aspect of the ACCEPT command in detail.

DEFAULT default_value

Specifies a default value to assign to the variable. This is used if the user bypasses the prompt by pressing ENTER without actually entering a response. The default value should usually be enclosed within single quotes.

PROMPT prompt_text

This is the prompt text displayed to the user before waiting for input.

NOPROMPT

Indicates that you do not want the user to see a visible prompt.

HIDE

Causes SQL*Plus not to echo the user’s response back to the display. This is useful if you are prompting for a password.

Tip

The syntax for the ACCEPT command has evolved significantly with the past few releases of SQL*Plus. The syntax shown here is valid for version 8.1. Not all of the clauses are available when using prior versions. Be sensitive to this, and check your documentation if you are writing scripts that need to work under earlier versions of SQL*Plus.

Using ACCEPT to get the table name

You can make the LIST_INDEXES 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 just prior to the COLUMN commands, so the resulting script would look like this:

SET HEADING OFF
SET RECSEP OFF
SET NEWPAGE 1

--Get the table name from the user
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;
COMMIT;
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 just as well, and the script just shown uses both. When you run the script, here’s how the prompt will look:

SQL> @c:\jonathan\sql_plus_book\xe_ch_5\list_indexes_f
Enter the table name >

Now you can run this script many times in succession, and you will be prompted for a different table name each time. In addition, this prompt is a bit more user-friendly than the default prompt generated by SQL*Plus.

The PROMPT Command

The PROMPT command is used to print text on the display for the user to read. It allows you to provide informative descriptions of what a script is about to do. It can be used to provide very long and detailed prompts for information, and it can be used simply to add blank lines to the output in order to space things out a bit better.

Syntax for the PROMPT command

PROMPT is a very simple command. The syntax looks like this:

PRO[MPT] text_to_be_displayed

where:

PRO[MPT]

Is the command, which may be abbreviated to PRO.

text_to_be_displayed

Is whatever text you want displayed for the user to see. This should not be a quoted string. If you include quotes, they will appear in the output.

If you are spooling output to a file when a PROMPT command is executed, the prompt text will also be written to the file. Any substitution variables in the prompt text will be replaced by their respective values before the text is displayed.

Using PROMPT to summarize the script

It would be nice to add some messages to the LIST_INDEXES script to make it 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 simply space the output a bit better by adding a blank line above and below the description.

Using PROMPT to explain the output

The PROMPT command can also be used to better explain the output of a script. In the LIST_INDEXES example, messages could be added prior to the DESCRIBE command, and prior to the SELECT statement, in order to explain the output. The resulting script would look like this:

...
PROMPT
PROMPT &table_name table definition:
PROMPT
DESCRIBE &&table_name

PROMPT
PROMPT Indexes defined on the &table_name table:
PROMPT
SELECT ui.index_name,
...

Here is the result of executing the script with all the PROMPT commands added. The messages not only make the output more clear, but space it out better as well.

SQL> @c:\jonathan\sql_plus_book\xe_ch_5\list_indexes_G

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
 EMPLOYEE_ID                     NOT NULL NUMBER
 TIME_LOG_DATE                   NOT NULL DATE
 HOURS_LOGGED                             NUMBER
 DOLLARS_CHARGED                          NUMBER


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

       INDEX: PROJECT_HOURS_BY_DATE
  TABLESPACE: USER_DATA
     COLUMNS: TIME_LOG_DATE

       INDEX: PROJECT_HOURS_EMP_DATE
  TABLESPACE: USER_DATA
     COLUMNS: EMPLOYEE_ID
              TIME_LOG_DATE

UNIQUE INDEX: PROJECT_HOURS_PK
  TABLESPACE: USER_DATA
     COLUMNS: PROJECT_ID
              EMPLOYEE_ID
              TIME_LOG_DATE

6 rows selected.


Commit complete.