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
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 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.
Here is the syntax for the ACCEPT command:
ACC[EPT]user_variable
[NUM[BER]|CHAR|DATE] [FOR[MAT]format_specification
] [DEF[AULT]default_value
] [PROMPTprompt_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.
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 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.
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.
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.
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.
Get Oracle SQL*Plus: The Definitive Guide now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.