Reporting Techniques - Oracle Database Foundations - page 198

The on Report clause was added to the BREAK command so that totals would
be generated by the COMPUTE statement that follows it. Janice “breaks” on the
report only once, but she still needs to specify it, because the COMPUTE statement
performs the aggregate operation only at a BREAK in a report. The COMPUTE state-
ments in Janice's revised report perform a sum of the salary amounts and provide
a custom label when the department salary sum is displayed on the report.

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

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 exe-
cuted. A substitution variable preceded
by & will not prompt the user for a value
if the same substitution variable, pre-
ceded by &&, exists earlier in the script.

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;