Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

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

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.

The syntax for the ACCEPT command has evolved significantly from the early days of SQL*Plus. The syntax shown in this chapter is valid for Version 8.1 and higher. Not all of the clauses are available when using prior versions. Check your documentation if you are writing scripts that need to work under earlier versions of SQL*Plus.

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.

If you are spooling output to a file when a PROMPT command is executed, the prompt text will be written to the file.

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