Using Substitution Variables

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

8.2.1 What Is a Substitution Variable?

A substitution variable is not like a true variable used in a programming language. Instead, substitution variables mark places in the text where SQL*Plus does the equivalent of 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 one or two ampersand characters . Say, for example, 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 INNER JOIN project_hours ph ON p.project_id = ph.project_id WHERE ph.employee_id = 107;

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, and 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. That script might look like this:

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

The ampersand in front of &employee_id marks it as a variable. At runtime, when it reads the statement, SQL*Plus sees the substitution variable and replaces it with the current value of that variable. If employee_id contains a value of 104, then &employee_id is replaced by "104", and the resulting line looks like this:

WHERE ph.employee_id = 104

As I said earlier, and as you can see, SQL*Plus does a search-and-replace operation. The Oracle database doesn't know that a variable has been used. Nor does SQL*Plus compare the contents of the employee_id column against the value of the variable. SQL*Plus 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 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.

8.2.2 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 Project Hours and Dollars Detail report shown in Example 7-2. You will see how you can modify the script to print the report for 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.

8.2.2.1 The report for one specific employee

The report from Example 7-2 produced detailed hours and dollars information for all employees . To reduce the scope to one employee (e.g., employee 107), you can add this line as the WHERE clause in the report's underlying query:

WHERE e.employee_id = 107

Example 8-1 shows the resulting script, to which I've added a SPOOL command to write the report output to a file.

Example 8-1. Using a substitution variable to mark a user-supplied value

SET ECHO OFF SET RECSEP OFF --Set up pagesize parameters SET NEWPAGE 1 SET PAGESIZE 55 --Set the linesize, which must match the number of equals signs used --for the ruling lines in the headers and footers. SET LINESIZE 66 --Set up 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 - 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 "DollarsCharged" FORMAT 9,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 COMPUTE SUM LABEL 'Totals' OF hours_logged ON project_id COMPUTE SUM LABEL 'Totals' OF dollars_charged ON project_id 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 ex8-1.lst SELECT e.employee_id, e.employee_name, p.project_id, p.project_name, TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date, ph.hours_logged, ph.dollars_charged FROM employee e INNER JOIN project_hours ph ON e.employee_id = ph.employee_id INNER JOIN project p ON p.project_id = ph.project_id WHERE e.employee_id = 107 ORDER BY e.employee_id, p.project_id, ph.time_log_date; SPOOL OFF EXIT

Running the script in Example 8-1 will produce a report specifically for employee 107:

The Fictional Company I.S. Department Project Hours and Dollars Detail ================================================================== Employee: 107 Lesia Ukrainka Dollars Proj ID Project Name Date Hours Charged ------- -------------------------- ----------- ------ ------------ 1001 Corporate Web Site 02-Jan-2004 1 .00 02-Mar-2004 3 5.00 02-May-2004 5 5.00 . . .

The next step is to generalize the script to make it usable for any employee.

8.2.2.2 Generalizing the report with substitution variables

You don't want to 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:

WHERE 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 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, using &id for the variable would leave the user wondering whether to enter an employee ID or a project ID. The use of &employee_id clarifies the answer. 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.

8.2.2.3 Running the report

In the examples for this book, the modified report script can be found in ex8-1b.sql . When you run the report, SQL*Plus will prompt you to provide a value for the &employee_id substitution variable. Here's how the script execution and prompt will look:

oracle@gennick02:~/sqlplus/ExampleScripts> sqlplus gennick/secret @ex8-1b SQL*Plus: Release 10.1.0.2.0 - Production on Sun Jun 27 22:53:12 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options Enter value for employee_id: 108

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 displays 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 12: WHERE e.employee_id = &employee_id new 12: WHERE e.employee_id = 108

Next, SQL*Plus goes on to read the remaining lines from the script, producing the following hours and dollars report for Pavlo Chubynsky.

The Fictional Company I.S. Department Project Hours and Dollars Detail ================================================================== Employee: 108 Pavlo Chubynsky Dollars Proj ID Project Name Date Hours Charged ------- -------------------------- ----------- ------ ------------ 1001 Corporate Web Site 01-Jan-2004 1 0.00 01-Mar-2004 3 0.00 01-May-2004 5 ,100.00 01-Jul-2004 7 ,540.00 01-Sep-2004 1 0.00 01-Nov-2004 3 0.00 ******* ************************** ------ ------------ Totals 20 ,400.00 . . .

In addition to being displayed on the screen, the report is spooled to the ex8-1b.lst file, as specified in the script.

8.2.2.4 When TERMOUT is off

In the example just shown, the report was displayed on the screen and spooled to a file. In Chapter 5 you saw how the SET TERMOUT OFF command could be used to suppress output to the display while allowing it to be spooled, thus making a report run much faster. Doing the same thing in this case presents a special problem. The problem is that the command SET 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 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.

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 the section titled "Prompting for Values."

8.2.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 usually don't want to prompt the user separately for each occurrence.

8.2.3.1 An example that prompts twice for the same value

Take a look at the script in Example 8-2, which displays information about a table followed by a list of all indexes defined on the table.

Example 8-2. Prompting twice for the same substitution variable

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

Example 8-2 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 table'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> @ex8-2 Enter value for table_name: project_hours 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)

Because only a single ampersand was used in the script, the value entered is used for that one specific instance. It is not saved for future reference. The result is that next time SQL*Plus encounters &table_name , it must prompt again, this time 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 an SQL statement. When the DESCRIBE command was read, the script 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 index defined on the project_hours table, looks like this:

UNIQUE INDEX: PROJECT_HOURS_PK TABLESPACE: USERS COLUMNS: PROJECT_ID EMPLOYEE_ID TIME_LOG_DATE

8.2.3.2 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 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 saves the value. All subsequent references to the same variable use that same value. It doesn't matter if subsequent references use a double ampersand or a single ampersand. 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.

You will be prompted only once for the table name, as the following output shows:

SQL> @ex8-2b Enter value for table_name: project_hours 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) 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

8.2.3.3 A final caveat

If you run the ex8-2b.sql 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 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 depend 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.