Writing Interactive Commands

The following features of SQL*Plus make it possible for you to set up command files that allow end-user input:

• defining user variables

• substituting values in commands

• using the START command to provide values

• prompting for values

Defining User Variables

You can define variables, called user variables, for repeated use in a single command file by using the SQL*Plus DEFINE command. Note that you can also define user variables to use in titles and to save your keystrokes (by defining a long string as the value for a variable with a short name).

Example 3-11 Defining a User Variable

To define a user variable L_NAME and give it the value "SMITH", enter the following command:

DEFINE L_NAME = SMITH

To confirm the variable definition, enter DEFINE followed by the variable name:

DEFINE L_NAME


DEFINE L_NAME = "SMITH" (CHAR)

To list all user variable definitions, enter DEFINE by itself at the command prompt. Note that any user variable you define explicitly through DEFINE takes only CHAR values (that is, the value you assign to the variable is always treated as a CHAR datatype). You can define a user variable of datatype NUMBER implicitly through the ACCEPT command. You will learn more about the ACCEPT command later in this chapter.

To delete a user variable, use the SQL*Plus command UNDEFINE followed by the variable name.

Using Substitution Variables

Suppose you want to write a query like the one in SALES (see Example 3-7) to list the employees with various jobs, not just those whose job is SA_MAN. You could do that by editing a different CHAR value into the WHERE clause each time you run the command, but there is an easier way.

By using a substitution variable in place of the value SA_MAN in the WHERE clause, you can get the same results you would get if you had written the values into the command itself.

A substitution variable is a user variable name preceded by one or two ampersands (&). When SQL*Plus encounters a substitution variable in a command, SQL*Plus executes the command as though it contained the value of the substitution variable, rather than the variable itself.

For example, if the variable SORTCOL has the value JOB_ID and the variable MYTABLE has the value EMP_DETAILS_VIEW, SQL*Plus executes the commands

SELECT &SORTCOL, SALARY
FROM &MYTABLE
WHERE SALARY>12000;

as if they were

SELECT JOB_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;

Where and How to Use Substitution Variables

You can use substitution variables anywhere in SQL and SQL*Plus commands, except as the first word entered at the command prompt. When SQL*Plus encounters an undefined substitution variable in a command, SQL*Plus prompts you for the value.

You can enter any string at the prompt, even one containing blanks and punctuation. If the SQL command containing the reference should have quote marks around the variable and you do not include them there, the user must include the quotes when prompted.

SQL*Plus reads your response from the keyboard, even if you have redirected terminal input or output to a file. If a terminal is not available (if, for example, you run the command file in batch mode), SQL*Plus uses the redirected file.

After you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. You can suppress this listing by setting the SET command variable VERIFY to OFF.

You should avoid creating substitution variables with names that may be identical to values that you will pass to them, as unexpected results can occur. If a value supplied for a substitution variable matches a variable name, then the contents of the matching variable are used instead of the supplied value.

Example 3-12 Using Substitution Variables

Create a command file named STATS, to be used to calculate a subgroup statistic (the maximum value) on a numeric column:

CLEAR BUFFER
INPUT
SELECT &GROUP_COL, MAX(&NUMBER_COL) MAXIMUM
FROM &TABLE
GROUP BY &GROUP_COL
.

SAVE STATS



Created file STATS

Now run the command file STATS:

@STATS

And respond to the prompts for values as shown:

Enter value for group_col: JOB_ID
old   1: SELECT   &GROUP_COL,
new   1: SELECT   JOB_ID,
Enter value for number_col: SALARY
old   2:          MAX(&NUMBER_COL) MAXIMUM
new   2:          MAX(SALARY) MAXIMUM
Enter value for table: EMP_DETAILS_VIEW
old   3: FROM     &TABLE
new   3: FROM     EMP_DETAILS_VIEW
Enter value for group_col: JOB_ID
old   4: GROUP BY &GROUP_COL
new   4: GROUP BY JOB_ID

SQL*Plus displays the following output:

JOB_ID        MAXIMUM                                                           
---------- ----------                                                           
AC_ACCOUNT       8300                                                           
AC_MGR          12000                                                           
AD_ASST          4400                                                           
AD_PRES         24000                                                           
AD_VP           17000                                                           
FI_ACCOUNT       9000                                                           
FI_MGR          12000                                                           
HR_REP           6500                                                           
IT_PROG          9000                                                           
MK_MAN          13000                                                           
MK_REP           6000                                                           

JOB_ID        MAXIMUM                                                           
---------- ----------                                                           
PR_REP          10000                                                           
PU_CLERK         3100                                                           
PU_MAN          11000                                                           
SA_MAN          14000                                                           
SA_REP          11500                                                           
SH_CLERK         4200                                                           
ST_CLERK         3600                                                           
ST_MAN           8200         

19 rows selected.                                                          

If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example:

SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='&X.5';
Enter value for X:  20

is interpreted as

SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='205';

Avoiding Unnecessary Prompts for Values

Suppose you wanted to expand the file STATS to include the minimum, sum, and average of the "number" column. You may have noticed that SQL*Plus prompted you twice for the value of GROUP_COL and once for the value of NUMBER_COL in Example 3-12, and that each GROUP_COL or NUMBER_COL had a single ampersand in front of it. If you were to add three more functions--using a single ampersand before each--to the command file, SQL*Plus would prompt you a total of four times for the value of the number column.

You can avoid being re-prompted for the group and number columns by adding a second ampersand in front of each GROUP_COL and NUMBER_COL in STATS. SQL*Plus automatically DEFINEs any substitution variable preceded by two ampersands, but does not DEFINE those preceded by only one ampersand. When you have DEFINEd a variable, SQL*Plus substitutes the value of variable for each substitution variable referencing variable (in the form &variable or &&variable). SQL*Plus will not prompt you for the value of variable in this session until you UNDEFINE variable.

Example 3-13 Using Double Ampersands

To expand the command file STATS using double ampersands and then run the file, first suppress the display of each line before and after substitution:

SET VERIFY OFF

Now retrieve and edit STATS by entering the following commands:

GET STATS
SELECT   &GROUP_COL,
MAX(&NUMBER_COL) MAXIMUM
FROM     &TABLE
GROUP BY &GROUP_COL



2
  2* MAX(&NUMBER_COL) MAXIMUM
APPEND ,
  2* MAX(&NUMBER_COL) MAXIMUM,
CHANGE/&/&&
  2* MAX(&&NUMBER_COL) MAXIMUM,
I
  3i MIN (&&NUMBER_COL) MINIMUM,
  4i SUM(&&NUMBER_COL)  TOTAL,
  5i AVG(&&NUMBER_COL)  AVERAGE
  6i
  1* SELECT   &GROUP_COL,
CHANGE/&/&&
  1* SELECT   &&GROUP_COL,
7
  7* GROUP BY &GROUP_COL
CHANGE/&/&&/
  7* GROUP BY &&GROUP_COL
SAVE STATS2
Created file STATS2

Finally, run the command file STATS2 and respond to the prompts as follows:

START STATS2
Enter value for group_col: JOB_ID
Enter value for number_col: SALARY
Enter value for table: EMP_DETAILS_VIEW

SQL*Plus displays the following output:

JOB_ID        MAXIMUM    MINIMUM      TOTAL    AVERAGE                          
---------- ---------- ---------- ---------- ----------                          
AC_ACCOUNT       8300       8300       8300       8300                          
AC_MGR          12000      12000      12000      12000                          
AD_ASST          4400       4400       4400       4400                          
AD_PRES         24000      24000      24000      24000                          
AD_VP           17000      17000      34000      17000                          
FI_ACCOUNT       9000       6900      39600       7920                          
FI_MGR          12000      12000      12000      12000                          
HR_REP           6500       6500       6500       6500                          
IT_PROG          9000       4200      28800       5760                          
MK_MAN          13000      13000      13000      13000                          
MK_REP           6000       6000       6000       6000                          

JOB_ID        MAXIMUM    MINIMUM      TOTAL    AVERAGE                          
---------- ---------- ---------- ---------- ----------                          
PR_REP          10000      10000      10000      10000                          
PU_CLERK         3100       2500      13900       2780                          
PU_MAN          11000      11000      11000      11000                          
SA_MAN          14000      10500      61000      12200                          
SA_REP          11500       6100     250500       8350                          
SH_CLERK         4200       2500      64300       3215                          
ST_CLERK         3600       2100      55700       2785                          
ST_MAN           8200       5800      36400       7280                          

19 rows selected.                             

Note that you were prompted for the values of NUMBER_COL and GROUP_COL only once. If you were to run STATS2 again during the current session, you would be prompted for TABLE (because its name has a single ampersand and the variable is therefore not DEFINEd) but not for GROUP_COL or NUMBER_COL (because their names have double ampersands and the variables are therefore DEFINEd).

Before continuing, set the system variable VERIFY back to ON:

SET VERIFY ON

Restrictions

You cannot use substitution variables in the buffer editing commands, APPEND, CHANGE, DEL, and INPUT, nor in other commands where substitution would be meaningless, such as in SQL*Plus comments (REMARK, /*... */ or --). The buffer editing commands, APPEND, CHANGE, and INPUT, treat text beginning with "&" or "&&" literally, as any other text string.

System Variables

The following system variables, specified with the SQL*Plus SET command, affect substitution variables:

SET DEFINE	Defines the substitution character (by default the ampersand "&") and turns substitution on and off.
SET ESCAPE	Defines an escape character you can use before the substitution character. The escape character instructs SQL*Plus to treat the substitution character as an ordinary character rather than as a request for variable substitution. The default escape character is a backslash (\).
SET VERIFY ON	Lists each line of the command file before and after substitution.
SET CONCAT	Defines the character that separates the name of a substitution variable or parameter from characters that immediately follow the variable or parameter--by default the period (.).

For more information about system variables, see the SET command in the "Command Reference" in Chapter 8.

Passing Parameters through the START Command

You can bypass the prompts for values associated with substitution variables by passing values to parameters in a command file through the START command.

You do this by placing an ampersand (&) followed by a numeral in the command file in place of a substitution variable. Each time you run this command file, START replaces each &1 in the file with the first value (called an argument) after START filename, then replaces each &2 with the second value, and so forth.

For example, you could include the following commands in a command file called MYFILE:

SELECT * FROM EMP_DETAILS_VIEW
WHERE JOB_ID='&1'
AND SALARY='&2';

In the following START command, SQL*Plus would substitute CLERK for &1 and 7900 for &2 in the command file MYFILE:

START MYFILE PU_CLERK 3100

When you use arguments with the START command, SQL*Plus DEFINEs each parameter in the command file with the value of the appropriate argument.

Example 3-14 Passing Parameters through START

To create a new command file based on SALES that takes a parameter specifying the job to be displayed, enter

GET SALES





  1  COLUMN LAST_NAME HEADING 'LAST NAME'
  2  COLUMN SALARY  HEADING 'MONTHLY SALARY' FORMAT $99,999
  3  COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90
  4  SELECT LAST_NAME, SALARY, COMMISSION_PCT
  5  FROM EMP_DETAILS_VIEW
  6* WHERE JOB_ID='SA_MAN'
6
  6* WHERE JOB_ID='SA_MAN'
CHANGE /SA_MAN/&1
  6* WHERE JOB_ID='&1'
SAVE ONEJOB
Created file ONEJOB

Now run the command with the parameter CLERK:

START ONEJOB SA_MAN

SQL*Plus lists the line of the SQL command that contains the parameter, before and after replacing the parameter with its value, and then displays the output:

old   3: WHERE JOB_ID='&1'
new   3: WHERE JOB_ID='SA_MAN'

LAST NAME                 MONTHLY SALARY COMMISSION %
------------------------- -------------- ------------
Russell                          $14,000         0.40
Partners                         $13,500         0.30
Errazuriz                        $12,000         0.30
Cambrault                        $11,000         0.30
Zlotkey                          $10,500         0.20

You can use any number of parameters in a command file. Within a command file, you can refer to each parameter any number of times, and can include the parameters in any order.

Note: You cannot use parameters when you run a command with RUN or slash (/). You must store the command in a command file and run it with START or @.

Before continuing, return the columns to their original heading by entering the following command:

CLEAR COLUMN

Communicating with the User

Three SQL*Plus commands--PROMPT, ACCEPT, and PAUSE--help you communicate with the end user. These commands enable you to send messages to the screen and receive input from the user, including a simple Return. You can also use PROMPT and ACCEPT to customize the prompts for values SQL*Plus automatically generates for substitution variables.

Prompting for and Accepting User Variable

Through PROMPT and ACCEPT, you can send messages to the end user and accept values as end-user input. PROMPT displays a message you specify on-screen; use it to give directions or information to the user. ACCEPT prompts the user for a value and stores it in the user variable you specify. Use PROMPT in conjunction with ACCEPT when your prompt for the value spans more than one line.

Example 3-15 Prompting for and Accepting Input

To direct the user to supply a report title and to store the input in the variable MYTITLE for use in a subsequent query, first clear the buffer:

CLEAR BUFFER

Next, set up a command file as shown and save this file as PROMPT1:

INPUT




  4  PROMPT Enter a title of up to 30 characters
  5  ACCEPT MYTITLE PROMPT 'Title: '
  6  TTITLE LEFT MYTITLE SKIP 2
  7  SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
  8  FROM EMP_DETAILS_VIEW
  9  WHERE JOB_ID='SA_MAN'
  10



SAVE PROMPT1




Created file PROMPT1.sql

The TTITLE command sets the top title for your report. For more information about the TTITILE command, see "Defining Page and Report Titles and Dimensions" in Chapter 4.

Finally, run the command file, responding to the prompt for the title as shown:

START PROMPT1

Enter a title of up to 30 characters
Title: Department Report
Department Report

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        145 John                 Russell                        14000
        146 Karen                Partners                       13500
        147 Alberto              Errazuriz                      12000
        148 Gerald               Cambrault                      11000
        149 Eleni                Zlotkey                        10500

Before continuing, turn the TTITLE command off:

TTITLE OFF

Customizing Prompts for Substitution Variable

If you want to customize the prompt for a substitution variable value, use PROMPT and ACCEPT in conjunction with the substitution variable, as shown in the following example.

Example 3-16 Using PROMPT and ACCEPT in Conjunction with Substitution Variables

As you have seen in Example 3-15, SQL*Plus automatically generates a prompt for a value when you use a substitution variable. You can replace this prompt by including PROMPT and ACCEPT in the command file with the query that references the substitution variable. First clear the buffer with:

CLEAR BUFFER

To create such a file, enter the following:

INPUT
PROMPT Enter a valid employee ID
PROMPT For Example 145, 206
ACCEPT ENUMBER NUMBER PROMPT 'Employee ID. :'
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE EMPLOYEE_ID=&ENUMBER
.

Save this file as PROMPT2. Next, run this command file. SQL*Plus prompts for the value of ENUMBER using the text you specified with PROMPT and ACCEPT:

START PROMPT2

SQL*Plus prompts you to enter an Employee ID:

Enter a valid employee ID
For Example 145, 206

Employee ID. :205



old   3: WHERE EMPLOYEE_ID=&ENUMBER
new   3: WHERE EMPLOYEE_ID=       205

Department Report

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Shelley              Higgins                        12000

What would happen if you typed characters instead of numbers? Since you specified NUMBER after the variable name in the ACCEPT command, SQL*Plus will not accept a non-numeric value:

Try entering characters instead of numbers to the prompt for "Employee. ID.", SQL*Plus will respond with an error message and prompt you again to re-enter the correct number:

START PROMPT2

When SQL*Plus prompts you to enter an Employee ID, enter the word "one" instead of a number:

Enter a valid employee ID
For Example 145, 206

Employee ID. :one

SP2-0425: "one" is not a valid number

Sending a Message and Accepting Return as Input

If you want to display a message on the user's screen and then have the user enter Return after reading the message, use the SQL*Plus command PAUSE. For example, you might include the following lines in a command file:

PROMPT Before continuing, make sure you have your account card.

PAUSE Press RETURN to continue.

Clearing the Screen

If you want to clear the screen before displaying a report (or at any other time), include the SQL*Plus CLEAR command with its SCREEN clause at the appropriate point in your command file, using the following format:

CLEAR SCREEN

Before continuing to the next section, reset all columns to their original formats and headings by entering the following command:

CLEAR COLUMNS