SQL*Plus User's Guide and Reference

Contents Index Home Previous Next

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

You can define variables, called user variables, for repeated use in a single command file by using the SQL*Plus command DEFINE. Note that you can also define user variables to use in titles and to save you 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 EMPLOYEE and give it the value "SMITH", enter the following command:

SQL> DEFINE EMPLOYEE = SMITH

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

SQL> DEFINE EMPLOYEE

SQL*Plus lists the definition:

DEFINE EMPLOYEE       = "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 SALESMAN. 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 SALESMAN 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 and the variable MYTABLE has the value EMP, SQL*Plus executes the commands

SQL> BREAK ON &SORTCOL
SQL> SELECT &SORTCOL, SAL
  2  FROM &MYTABLE
  3  ORDER BY &SORTCOL;

as if they were

SQL> BREAK ON JOB
SQL> SELECT JOB, SAL
  2  FROM EMP
  3  ORDER BY JOB;

(The BREAK command suppresses duplicate values of the column named in SORTCOL; BREAK is discussed[*].)

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.

Example 3-12 Using Substitution Variables

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

SQL> CLEAR BUFFER
SQL> INPUT
  1  SELECT   &GROUP_COL,
  2           MAX(&NUMBER_COL) MAXIMUM
  3  FROM     &TABLE
  4  GROUP BY &GROUP_COL
  5
SQL> SAVE STATS
Created file STATS

Now run the command file STATS and respond as shown below to the prompts for values:

SQL>  @STATS
Enter value for group_col: JOB
old   1: SELECT   &GROUP_COL,
new   1: SELECT   JOB,
Enter value for number_col: SAL
old   2:          MAX(&NUMBER_COL) MAXIMUM
new   2:          MAX(SAL) MAXIMUM
Enter value for table: EMP
old   3: FROM     &TABLE
new   3: FROM     EMP
Enter value for group_col: JOB
old   4: GROUP BY &GROUP_COL
new   4: GROUP BY JOB

SQL*Plus displays the following output:

JOB           MAXIMUM
---------- ----------
ANALYST          3000
CLERK            1300
MANAGER          2975
PRESIDENT        5000
SALESMAN         1600

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

SQL> SELECT * FROM EMP WHERE EMPNO='&X.01';
Enter value for X:  123

will be interpreted as

SQL> SELECT * FROM EMP WHERE EMPNO='12301';

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 reprompted 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:

SQL> SET VERIFY OFF

Now retrieve and edit STATS by entering the following commands:

SQL> GET STATS
  1  SELECT &GROUP_COL,
  2         MAX(&NUMBER_COL) MAXIMUM
  3  FROM   &TABLE
  4  GROUP BY &GROUP_COL
SQL> 2
  2*  MAX(&NUMBER_COL) MAXIMUM
SQL> APPEND ,
  2*  MAX(&NUMBER_COL) MAXIMUM,
SQL> C /&/&&
  2*  MAX(&&NUMBER_COL) MAXIMUM,
SQL> I
  3i  MIN(&&NUMBER_COL) MINIMUM,
  4i  SUM(&&NUMBER_COL) TOTAL,
  5i  AVG(&&NUMBER_COL) AVERAGE
  6i
SQL> 1
  1* SELECT &GROUP_COL,
SQL> C /&/&&
  1* SELECT &&GROUP_COL,
SQL> 7
  7*  GROUP BY &GROUP_COL
SQL> C /&/&&
  7*  GROUP BY &&GROUP_COL
SQL> SAVE STATS2
created file STATS2

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

SQL> START STATS2
Enter value for group_col: JOB
Enter value for number_col: SAL
Enter value for table: EMP

SQL*Plus displays the following output:

JOB           MAXIMUM    MINIMUM      TOTAL    AVERAGE
---------- ---------- ---------- ----------  ---------
ANALYST          3000       3000       6000       3000
CLERK            1300        800       4150     1037.5
MANAGER          2975       2450       8275 2758.33333
PRESIDENT        5000       5000       5000       5000
SALESMAN         1600       1250       5600       1400

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:

SQL> 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 REMARK. 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 (.).
Refer to SET[*] for more information on these system variables.

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
WHERE JOB='&1'
AND SAL=&2

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

SQL> START MYFILE CLERK 7900

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

SQL> GET SALES
  1  COLUMN ENAME HEADING SALESMAN
  2  COLUMN SAL HEADING SALARY FORMAT $99,999
  3  COLUMN COMM HEADING COMMISSION FORMAT $99,990
  4  SELECT EMPNO, ENAME, SAL, COMM
  5  FROM EMP
  6* WHERE JOB = 'SALESMAN'
SQL> CHANGE /SALESMAN/&1
  6* WHERE  JOB = '&1'
SQL> 1
  1* COLUMN ENAME HEADING SALESMAN
SQL> CHANGE /SALESMAN/&1
  1* COLUMN ENAME HEADING &1
SQL> SAVE ONEJOB
Created file ONEJOB

Now run the command with the parameter CLERK:

SQL> START ONEJOB CLERK

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 = '&1'
new    3: WHERE JOB = 'CLERK'
    EMPNO CLERK          SALARY COMMISSION
--------- ---------- ---------- ----------
     7369 SMITH            $800
     7876 ADAMS          $1,100
     7900 JAMES            $950
     7934 MILLER         $1,300

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 column ENAME to its original heading by entering the following command:

SQL> COLUMN ENAME CLEAR

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 Values

Through PROMPT and ACCEPT, you can send messages to the end user and accept values as end-user input. PROMPT simply 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:

SQL> CLEAR BUFFER

Next, set up a command file as shown below:

SQL> INPUT
  1  PROMPT Enter a title up to 30 characters long.
  2  ACCEPT MYTITLE PROMPT 'Title:  '
  3  TTITLE LEFT MYTITLE SKIP 2
  4  SELECT * FROM DEPT
  5
SQL> SAVE PROMPT1
Created file PROMPT1

The TTITLE command sets the top title for your report. This command is covered in detail[*].

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

SQL> START PROMPT1
Enter a title up to 30 characters long.
Title:  Department Report as of 1/1/95

SQL*Plus displays the following output:

Department Report as of 1/1/95
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Before continuing, turn the TTITLE command you entered in the command file off as shown below:

SQL> TTITLE OFF

Customizing Prompts for Substitution Variable Values

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. To create such a file, enter the commands shown:

SQL> CLEAR BUFFER
buffer cleared
SQL> INPUT
  1  PROMPT Enter a valid employee number
  2  PROMPT For example:  7123, 7456, 7890
  3  ACCEPT ENUMBER NUMBER PROMPT 'Emp. no.:  '
  4  SELECT ENAME, MGR, JOB, SAL
  5  FROM EMP
  6  WHERE EMPNO = &ENUMBER
  7
SQL> SAVE PROMPT2
Created file PROMPT2

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

SQL> START PROMPT2
Enter a valid employee number
For example:  7123, 7456, 7890
Emp. No.:

Try entering characters instead of numbers to the prompt for "Emp. No.":

Emp. No.:  ONE
"ONE" is not a valid number
Emp. No.:

Because you specified NUMBER after the variable name in the ACCEPT command, SQL*Plus will not accept a non-numeric value. Now enter a number:

Emp. No.:  7521
old   3: WHERE EMPNO = &ENUMBER
new   3: WHERE EMPNO =       7521

SQL*Plus displays the following output:

ENAME             MGR JOB           SALARY
---------- ---------- --------- ----------
WARD             7698 SALESMAN      $1,250

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 chapter, reset all columns to their original formats and headings by entering the following command:

SQL> CLEAR COLUMNS


Contents Index Home Previous Next