Sql*Plus User’s Guide and Reference

Displaying Column Values in Titles
You may wish to create a master/detail report that displays a changing master column 
value at the top of each page with the detail query results for that value underneath. 
You can reference a column value in a top title by storing the desired value in a 
variable and referencing the variable in a TTITLE command. Use the following form of 
the COLUMN command to define the variable:
COLUMN 
column_name
NEW_VALUE 
variable_name
You must include the master column in an ORDER BY clause and in a BREAK 
command using the SKIP PAGE clause.
Example 6–24
Creating a Master/Detail Report
Suppose you want to create a report that displays two different managers' employee 
numbers, each at the top of a separate page, and the people reporting to the manager 
on the same page as the manager's employee number. First create a variable, 
MGRVAR, to hold the value of the current manager's employee number:
COLUMN MANAGER_ID NEW_VALUE MGRVAR NOPRINT
Because you will only display the managers' employee numbers in the title, you do not 
want them to print as part of the detail. The NOPRINT clause you entered above tells 
SQL*Plus not to print the column MANAGER_ID. 
Next, include a label and the value in your page title, enter the proper BREAK 
command, and suppress the bottom title from the last example:
TTITLE LEFT 'Manager: ' MGRVAR SKIP 2
BREAK ON MANAGER_ID SKIP PAGE
BTITLE OFF
Finally, enter and run the following query:
SELECT MANAGER_ID, DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE MANAGER_ID IN (101, 201)
ORDER BY MANAGER_ID, DEPARTMENT_ID;

---

Defining Page and Report Titles and Dimensions
6-24
SQL*Plus User's Guide and Reference
If you want to print the value of a column at the bottom of the page, you can use the 
COLUMN command in the following form:
COLUMN 
column_name
OLD_VALUE 
variable_name
SQL*Plus prints the bottom title as part of the process of breaking to a new 
page—after finding the new value for the master column. Therefore, if you simply 
referenced the NEW_VALUE of the master column, you would get the value for the 
next set of details. OLD_VALUE remembers the value of the master column that was 
in effect before the page break began.