O'Reilly logo

Oracle SQL*Plus: The Definitive Guide by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Using Substitution Variables

Substitution variables allow you to write generic SQL*Plus scripts. They allow you to mark places in a script where you want to substitute values at runtime.

What Is a Substitution Variable?

A substitution variable is the same thing as a user variable. In the previous chapter, you saw how to get the contents of a database column into a user variable and how to place the contents of that user variable into the page header of a report. SQL*Plus also allows you to place user variables in your script to mark places where you want to supply a value at runtime. When you use them this way, they are called substitution variables.

A substitution variable is not like a true variable used in a programming language. Instead, a substitution variable marks places in the text where SQL*Plus does the equivalent of a search and replace at runtime, replacing the reference to a substitution variable with its value.

Substitution variables are set off in the text of a script by preceding them with either one or two ampersand characters. Say, for example, that you had this query to list all projects to which employee #107 had charged time:

SELECT DISTINCT p.project_id, p.project_name
  FROM project p,
       project_hours ph
 WHERE ph.employee_id = 107
   AND p.project_id = ph.project_id;

As you can see, this query is specific to employee number 107. To run the query for a different employee, you would need to edit your script file, change the ID number, save the file, then execute it. That’s a pain. You don’t want to do that. Instead, you can generalize the script by rewriting the SELECT statement with a substitution variable in place of the employee ID number. It would look like this:

SELECT DISTINCT p.project_id, p.project_name
  FROM project p,
       project_hours ph
 WHERE ph.employee_id = &employee_id
   AND p.project_id = ph.project_id;

The ampersand in front of the word “employee_id” marks it as a variable. At run-time, when it reads the statement, SQL*Plus will see the substitution variable and replace it with the current value of the specified user variable. If the employee_id user variable contained a value of 104, then “&employee_id” would be replaced by “104”, and the resulting line would look like this:

WHERE ph.employee_id = 104

As stated earlier, and as you can see now, SQL*Plus truly does a “search and replace” operation. The Oracle database does not know that a variable has been used. Nor does SQL*Plus actually compare the contents of the employee_id column against the value of the variable. SQL*Plus simply does the equivalent of a search and replace operation on each statement before that statement is executed. As far as the Oracle database is concerned, you might just as well have included constants in your script.

Substitution variables are the workhorse of SQL*Plus scripts. They give you a place to store user input, and they give you a way to use that input in SQL queries, PL/SQL code blocks, and other SQL*Plus commands.

Using Single-Ampersand Variables

The easiest way to generalize a script is to take one you have working for a specific case and modify it by replacing specific values with substitution variables. In this section, we will revisit the Labor Hours and Dollars Detail report shown in Chapter 3. You will see how you can modify the script to print the report for only one employee, and you will see how you can use a substitution variable to generalize that script by making it prompt for the employee ID number at runtime.

When SQL*Plus encounters a variable with a single leading ampersand, it always prompts you for a value. This is true even when you use the same variable multiple times in your script. If you use it twice, you will be prompted twice. Double-ampersand variables allow you to prompt a user only once for a given value, and are explained later in this chapter.

The report for one specific employee

The report in the previous chapter produced detailed hours and dollars information for all employees. To reduce the scope to one employee, you can add this line to the WHERE clause:

AND e.employee_id = 107

Since this report is now only for one employee, the grand totals don’t make sense, so the COMPUTEs to create them can be removed. Finally, a SPOOL command has been added to capture the output in a file to be printed later. The complete script for the report looks like this:

--Setup pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55

--Set the linesize, which must match the number of equal signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 71

--Get the date for inclusion in the page footer.
SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';
COLUMN SYSDATE NEW_VALUE report_date
SELECT SYSDATE FROM DUAL;
SET TERMOUT ON

--Setup page headings and footings
TTITLE CENTER 'The Fictional Company' SKIP 3 -
       LEFT 'I.S. Department' -
       RIGHT 'Project Hours and Dollars Detail' SKIP 1 -
       LEFT `=============================================================' -
       SKIP 2 'Employee: ' FORMAT 9999 emp_id_var ' ' emp_name_var SKIP 3

BTITLE LEFT `=============================================================' -
       SKIP 1 -
       LEFT report_date -
       RIGHT 'Page ' FORMAT 999 SQL.PNO

--Format the columns
COLUMN employee_id NEW_VALUE emp_id_var NOPRINT
COLUMN employee_name NEW_VALUE emp_name_var NOPRINT
COLUMN project_id HEADING '     Proj ID' FORMAT 9999
COLUMN project_name HEADING 'Project Name' FORMAT A26 WORD_WRAPPED
COLUMN time_log_date HEADING 'Date' FORMAT A11
COLUMN hours_logged HEADING 'Hours' FORMAT 9,999
COLUMN dollars_charged HEADING 'Dollars|Charged' FORMAT $999,999.99

--Breaks and Computations
BREAK ON employee_id SKIP PAGE NODUPLICATES -
      ON employee_name NODUPLICATES -
      ON project_id SKIP 2 NODUPLICATES -
      ON project_name NODUPLICATES
CLEAR COMPUTES
COMPUTE SUM LABEL 'Project Totals' OF hours_logged ON project_name
COMPUTE SUM LABEL 'Project Totals' OF dollars_charged ON project_name
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id

--Execute the query to generate the report.
SPOOL C:\A\HOURS_DOLLARS
SELECT P.PROJECT_ID,
       P.PROJECT_NAME,
       TO_CHAR(PH.TIME_LOG_DATE,'dd-Mon-yyyy') time_log_date,
       PH.HOURS_LOGGED,
       PH.DOLLARS_CHARGED,
       E.EMPLOYEE_ID,
       E.EMPLOYEE_NAME
  FROM EMPLOYEE E,
       PROJECT P,
       PROJECT_HOURS PH
 WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
   AND P.PROJECT_ID = PH.PROJECT_ID
   AND E.EMPLOYEE_ID = 107
ORDER BY E.EMPLOYEE_ID, P.PROJECT_ID, PH.TIME_LOG_DATE;
SPOOL OFF

--Reset everything back to the defaults.
CLEAR BREAKS
CLEAR COMPUTES
TTITLE OFF
BTITLE OFF

SET NEWPAGE 1
SET PAGESIZE 24
SET LINESIZE 80

Running this script as shown will produce a report specifically for employee 107.

Generalizing the report with substitution variables

You don’t want to edit the script file and modify your script every time you need to produce a report for a different employee, and you don’t have to. Instead, you can replace the reference to a specific employee number with a substitution variable and let SQL*Plus prompt you for a value at runtime. Here’s how the affected line of script looks with a substitution variable instead of a hardcoded value:

AND E.EMPLOYEE_ID = &employee_id

The variable name should be descriptive, and it needs to serve two purposes. It needs to inform the user and it needs to inform you. First and foremost, the variable name is used in the prompt, and must convey to the user the specific information needed. In this case, for example, using &id for the variable would leave the user wondering whether to enter an employee ID or a project ID. The second thing to keep in mind is that you will need to look at the script again someday, so make sure the name is something that will jog your memory as well.

Running the report

When you run the report, SQL*Plus will prompt you for the value of the &employee_id substitution variable. Assume that the script is in a file named HOURS_DOLLARS.SQL. Here’s how the output will look:

SQL> @c:\hours_dollars_b
Enter value for employee_id: 111

As commands are executed, SQL*Plus constantly looks for the ampersand character, indicating a substitution variable. When an ampersand is encountered, the next token in the command is treated as a variable. SQL*Plus first looks to see if that variable has been previously defined. In this example it hasn’t, so SQL*Plus automatically prompts for the value.

After prompting for a value and substituting it into the script in place of the corresponding variable, SQL*Plus will display both the old and the new versions of the particular line of script involved. During development, this aids you in verifying that your script is executing correctly. Here are the before and after versions of the line containing the &employee_id variable from the current example:

old  13:    AND E.EMPLOYEE_ID = &employee_id
new  13:    AND E.EMPLOYEE_ID = 111

Next, SQL*Plus goes on to read the remaining lines from the script, producing this hours and dollars report for Taras Shevchenko:

The Fictional Company


I.S. Department                        Project Hours and Dollars Detail
=======================================================================

Employee:   111 Taras Shevchenko


                                                                Dollars
     Proj ID Project Name               Date         Hours      Charged
------------ -------------------------- ----------- ------ ------------
        1001 Corporate Web Site         01-Jan-1998      1      $100.00
                                        01-Mar-1998      3      $300.00
                                        01-May-1998      5      $500.00
                                        01-Jul-1998      7      $700.00
                                        01-Sep-1998      1      $100.00
                                        01-Nov-1998      3      $300.00
             **************************             ------ ------------
             Project Totals                             20    $2,000.00
...

In addition to being displayed on the screen, the report is also spooled to the file specified in the script.

When TERMOUT is off

In the example just shown, the report was both displayed on the screen and spooled to a file. In Chapter 3 you saw how the SET TERMOUT OFF command could be used to suppress output to the display while still allowing it to be spooled, thus making a report run much faster. Trying to do the same thing in this case presents a special problem. The problem is that the command to turn TERMOUT off must precede the SELECT statement that generates the report, so terminal output is off by the time SQL*Plus reads the line containing the substitution variable. SQL*Plus does not handle this situation too well. You won’t see a prompt for the substitution variable, because terminal output is off, but SQL*Plus will still be waiting for you to type in a value. Your session will appear to be hung. Here’s what you will see:

SQL> @c:\hours_dollars_c

Strangely enough, even if you remember that SQL*Plus needs an employee number and you type one in, it won’t be accepted. Try running the script like this:

SQL> @c:\hours_dollars_c
                  111

Even though you entered a value of 111, SQL*Plus will proceed as if you had entered an empty string. The end result will be the following error in the spool file:

Enter value for employee_id: 
old  13:    AND E.EMPLOYEE_ID = &employee_id
new  13:    AND E.EMPLOYEE_ID = 
ORDER BY E.EMPLOYEE_ID, P.PROJECT_ID, PH.TIME_LOG_DATE
*
ERROR at line 14:
ORA-00936: missing expression

Looking at the before and after versions of the line with the &employee_id variable, which are written to the spool file, you can see that the input of 111 was totally ignored. The result was a syntactically incorrect SQL statement, so instead of a report all you got was an error.

There is a solution to this problem. The solution is to use the ACCEPT command to explicitly prompt the user for the employee ID prior to issuing the SET TERMOUT OFF command. You will see how to do this later in this chapter in the section titled Section 4.3.

Using Double-Ampersand Variables

Using a double ampersand in front of a substitution variable tells SQL*Plus to define that variable for the duration of the session. This is useful when you need to reference a variable several times in one script, because you don’t usually want to prompt the user separately for each occurrence.

An example that prompts twice for the same value

Take a look at the following script, which displays information about a table followed by a list of all indexes defined on the table:

SET HEADING OFF
SET RECSEP OFF
SET NEWPAGE 1

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

This script uses &table_name twice, once in the DESCRIBE command that lists the columns for the table, and once in the SELECT statement that returns information about the tables’s indexes. When you run this script, SQL*Plus will issue separate prompts for each occurrence of &table_name. The first prompt will occur when SQL*Plus hits the DESCRIBE command:

SQL> @c:\list_indexes_d
Enter value for table_name: project_hours
 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

Since only a single ampersand was used, the value entered by the user was used for that one specific instance. It was not saved for future reference. The result is that next time SQL*Plus encounters &table_name, it must prompt again. This time it prompts for the table name to use in the SELECT statement:

Enter value for table_name: project_hours
old   9:    AND ui.table_name = UPPER('&table_name')
new   9:    AND ui.table_name = UPPER('project_hours')

Notice that SQL*Plus only displays before and after images of a line containing substitution variables when that line is part of a SQL query. When the DESCRIBE command was read, the user was prompted for a table name, and the substitution was made, but the old and new versions of the command were not shown.

The remaining output from the script, showing the indexes defined on the project_hours table, looks like this:

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.

A modified example that prompts once

Obviously there’s room for improvement here. You don’t want to type in the same value over and over just because it’s used more than once in a script. Aside from being inconvenient, doing so introduces the very real possibility that you won’t get it the same each time. One way to approach this problem is to use a double-ampersand the first time you reference the table_name variable in the script. Thus the DESCRIBE command becomes:

DESCRIBE &&table_name

The only difference between using a double ampersand rather than a single ampersand is that when a double ampersand is used, SQL*Plus will save the value. All subsequent references to the same variable use that same value. It doesn’t even matter if subsequent references use a double ampersand or a single. Once the table_name variable has been defined this way, any other reference to &table_name or &&table_name will be replaced with the defined value.

Now if you run the LIST_INDEXES script, you will only be prompted once for the table name, as the following output shows:

SQL> @c:\list_indexes_e
Enter value for table_name: project_hours
 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

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.

A final caveat

If you run the LIST_INDEXES script again, you won’t be prompted for a table name at all. Instead, the value entered earlier will be reused, and you will again see information about the project_hours table and its indexes. The reason for this is that once you define a variable, that definition sticks around until you either exit SQL*Plus or explicitly undefine the variable.

Because variable definitions persist after a script has ended, it’s usually best to explicitly prompt a user for input rather than depending on SQL*Plus to do it for you. The ACCEPT command is used for this purpose and is described in the next section. At the very least, you should UNDEFINE variables at the end of a script so they won’t inadvertently be reused later.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required