Substitution Variables

Another way to make an iSQL*Plus report more flexible is by using substitution variables. A substitution variable is a string preceded by either an ampersand (&) or a double ampersand (&&) in an iSQL*Plus script that will prompt the user for its value when the script is run.

substitution variable

A string literal with no embedded spaces, preceded by & or &&, that will prompt the user for a value when an iSQL*Plus script containing one of these variables is executed. A substitution variable preceded by & will not prompt the user for a value if the same substitution variable, preceded by &&, exists earlier in the script.

A substitution variable preceded by a single ampersand will prompt for a value every time it is encountered in a script. A substitution variable preceded by a double ampersand will prompt for a value once and will save that value. Once saved, if the same substitution variable preceded by a single ampersand is encountered, it will use the value saved when the substitution variable with the double ampersand was encountered.

Janice is reviewing the script she has been working on all day, and realizes that sooner or later, the boss will want to run that script for any list of departments, not just departments 30 and 60. She realizes that substitution variables would be useful in this situation, and she changes her script as follows to allow iSQL*Plus to prompt for the department numbers before the query runs:

set feedback off ttitle left ‘Department Salary Report’ btitle left ‘End Salary Report’ skip 1 -    left ‘Widgets-R-Us, Inc.’ column Dept heading ‘Dept|Number’ column salary format $999,999.99 break on Dept on Report compute sum label ‘Dept Total’ -    of salary on Dept compute sum label ‘All Depts’ -    of salary on Report select department_id "Dept",   last_name || ‘, ‘ || first_name "Employee",   salary "Salary" from employees where department_id in (&DeptList) order by department_id asc, salary desc;

The only change is the replacement of the specific department numbers in the original script with the substitution variable DeptList. When Janice clicks the Execute button in iSQL*Plus, she is prompted for the value of DeptList.

The script runs as before, except this time, a different group of departments is returned from the query.

Notice that iSQL*Plus, by default, will show the substitutions that occurred before presenting the results. This can be turned off with the SET VERIFY OFF command.

As you may have noticed, Janice is somewhat of a perfectionist, and she thinks that the report would look even better if the report header contained the list of departments in the report. This gives Janice a good opportunity to use the double ampersand in her substitution variable, so that she will not need to enter the department list twice when she runs the script. Her revised script now looks like this:

set feedback off ttitle left -  ‘Department Salary Report, Departments: &&DeptList’ btitle left ‘End Salary Report’ skip 1 -    left ‘Widgets-R-Us, Inc.’ column Dept heading ‘Dept|Number’ column salary format $999,999.99 break on Dept on Report compute sum label ‘Dept Total’ -    of salary on Dept compute sum label ‘All Depts’ -    of salary on Report select department_id "Dept",   last_name || ‘, ‘ || first_name "Employee",   salary "Salary" from employees where department_id in (&DeptList) order by department_id asc, salary desc;

She changed the TTITLE command to include the substitution variable &&DeptList. When this script is run, the prompt for DeptList occurs only once.

However, the substitution is performed twice. The first substitution variable &&DeptList has a double ampersand, and therefore its value is retained when &DeptList is encountered later in the script.