Lesson 7	Environment Settings for Reports
Objective	Define the Environment Settings for SQL*Plus Reports

Environment Settings for Reports

There are several other commands that you can use only within SQL*Plus for creating reports. The table below lists these commands.

Command	Description
TTI[TLE] [printspec [text\|variable] ...]\|[OFF\|ON]	This is the title that appears on the top of each page of a report. Printspec includes formatting commands such as `CENTER`, `LEFT`, `RIGHT`, `SKIP 1` (Skip one line), `COL` n (start next text at column n), BOLD. Turn off or on current `TTITLE` using `TTITLE` OFF and `TTITLE` ON.
BTI[TLE] [printspec [text\|variable] ...]\|[OFF\|ON]	Footer that appears at the bottom of each page. Same format as `TTITLE`.
BRE[AK] [ON {columnname\|ROW\|REPORT} [SKIP n \| SKIP PAGE ] [DUP[LICATES] \| NODUP[LICATES]]]	Stop the report printing whenever there is a new value in the columnname. Perform the action(s) specified and then continue. By default, the `BREAK` skips one line (`SKIP 1`) before beginning the next line. By default, when you specify a `BREAK` on a column, the column value is not repeated in subsequent rows (NODUP), until a new value is found.
COMP[UTE] [function [LAB[EL] text] ... OF columnname ON {columnname\|ROW\|REPORT} ...]	Compute group functions on columns. Value computed is printed when the corresponding break (set with the `BREAK` command) is hit. Group functions include `COUNT, MAX, MIN, AVG, SUM, STD, VAR`.
REPF[OOTER] [PAGE] [printspec [text\|variable] ...] \| [OFF\|ON]	Footer printed at the end of the report. Same format as `TTITLE`.
REPH[EADER] [PAGE] [printspec [text\|variable] ...] \| [OFF\|ON]	Header printed at the beginning of the report. Same format as `TTITLE`.

The Tool Tip below gives you a clear picture of how the three commands:

TTITLE,
BREAK, and
COMPUTE

work together.

Apply, Filter, Sort — This is the title of the report. The dash at the end of the first line is needed because the TTITLE command was continued to the second line.

The COMPUTE command here tells SQL*Plus to calculate the sum of the TOTAL_SALE column when there is a break on the LASTNAME column.

The BREAK command here says that the report will break on each new value of the LASTNAME column and then skip one line.

This query is executed.

The first line of the report title. The TTITLE command said to place these words, left justified on the first line of the report and then skip one line (meaning go to the next line).

This is the beginning of the second line of the report title. The TTITLE command (second line of the command) stated to print "Pg."

This is the page number, which is displayed because the TTITLE contains the SQL*Plus variable called SQL.PNO. This is one of a small handful of pre-defined variables within SQL*Plus.

This blank line is here because the TTITLE says "SKIP 2" at the end of the command.

These two lines are printed when the <tt>BREAK</tt> stops printing rows and the COMPUTE command prints its results (the sum of TOTAL_SALE).

This blank line is the action taken by the BREAK command (SKIP 1). After this line, the next row of the report is printed.

This is the title of the report. The dash at the end of the first line is needed because the TTITLE command was continued to the second line

SQL Plus Report Commands
Now that you know how to create a report, the next lesson shows how to save the report output to a file for printing later.

Oracle Report Headings

Click the Exercise link to practice matching types of headings with their functions.
Oracle Report Headings