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.

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


     



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net