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.

A Rose by Any Other Name

The type of variable that this chapter terms a substitution variable is commonly referred to by two other names : user variable and define variable . The SQL*Plus User's Guide and Reference (Oracle Corporation) favors the term substitution variable , but the Oracle Database 10 g version of that manual contains at least one reference to user variable ; past editions use the term more frequently. The term define variable is in common use because the DEFINE command represents one way to create such variables.

When talking to other Oracle professionals, I hear define variable most often, and, because such variables are so closely linked with the DEFINE command, that term tends to leave no doubt as to what kind of variable is being discussed. In this book, I use the term substitution variable partly because it's Oracle's preferred term and partly because it most accurately describes the function of such variables in a scripting context.


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.

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 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 up pagesize parameters



--Set the linesize, which must match the number of equals signs used

--for the ruling lines in the headers and footers.


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


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


 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,




 TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date,



 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;




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


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


sqlplus gennick/secret @ex8-1b

SQL*Plus: Release - 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 - Production

With the Partitioning, OLAP and Data Mining options

Enter value for employee_id:



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.

Be sure to run the examples in this chapter using command-line SQL*Plus. Many commands, notably SPOOL and SET NEWPAGE, are not available in i SQL*Plus, so these scripts that function perfectly well in the command-line environment will fail in the web environment.


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


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. 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.

Using the example scripts, you can demonstrate the problem of an apparent hung session by executing script ex8-1c.sql :






SQL*Plus will display the normal messages about the release of SQL*Plus and the release of the database to which you are connected. And that's it. SQL*Plus will be awaiting your input, but you'll never see a prompt.


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. 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




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 -


DESCRIBE &table_name

SELECT ui.index_name,


 DECODE(ui.uniqueness,'UNIQUE','UNIQUE',' ') uniqueness,

 ' COLUMNS:' column_header,


 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;







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:



Enter value for table_name: project_hours

 Name Null? Type

 ----------------------------------------- -------- ------------------







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:


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:





 TIME_LOG_DATE 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.

Execute ex8-2b.sql to see the behavior when using a double ampersand. Otherwise, the script is the same as ex8-2.sql .

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



Enter value for table_name: project_hours

 Name Null? Type

 ----------------------------------------- -------- ---------------






old 9: AND ui.table_name = UPPER('&table_name')

new 9: AND ui.table_name = UPPER('project_hours')





 TIME_LOG_DATE 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.

You can undefine the table_name variable by placing the following command at the end of the script:

UNDEFINE table_name

The version of the script in ex8-2c.sql has this command.


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.

Numeric Substitution Variables

SQL*Plus supports four datatypes for substitution variables: CHAR, NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. You can enter only character or CHAR values. You can't use any commands to create a substitution variable of the other types However, you can create one indirectly by using NEW_VALUE to capture a numeric value returned by a query:




SELECT 42 x FROM dual;





DEFINE my_age


Because numeric values are captured as NUMBERs, you can format those values using SQL*Plus's number-formatting features. You aren't limited by any kind of number-to-character conversion done by the database. The following TTITLE example formats the same value two different ways:

ttitle LEFT FORMAT 99 my_age FORMAT 99.99 my_age

This page title will contain the string "42" followed by "42.00".


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

show all menu

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
Similar book on Amazon © 2008-2017.
If you may any questions please contact us: