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 diagram below gives you a clear picture of how the three commands:

TTITLE,
BREAK, and
COMPUTE

work together.

SQL*Plus Report Commands

Compute Sum of Total Sale on Last Name
TITLE LEFT "Customer Purchasing" - SKIP 1 "Pq." SQL.PN0 SKIP 2 COMPUTE SUM OF TOTAL_SALE ON LASTNAME BREAK ON LASTNAME SKIP 1

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

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.
The following section discusses matching types of headings with their functions when generating reports in Oracle.

Oracle Report Headings

TTITLE, Specifies heading that is displayed once per page
COLUMN, Specifies formatting and attributes for items in the SELECT clause
BREAK, Specifies actions, such as skipping a line, taken when a column's value changes
COMPUTE, Specifies group functions to apply and display when a column's value changes
SET, Specifies environment parameters such as number of lines per page

Defining Page and Report Titles and Dimensions: The word page refers to a screen full of information on your display or a page of a spooled (printed) report. You can place top and bottom titles on each page, set the number of lines per page, and determine the width of each line. The word report refers to the complete results of a query. You can also place headers and footers on each report and format them in the same way as top and bottom titles on pages.
Setting Top and Bottom Titles and Headers and Footers: As you have already seen, you can set a title to display at the top of each page of a report. You can also set a title to display at the bottom of each page. The TTITLE command defines the top title; the BTITLE command defines the bottom title. You can also set a header and footer for each report. The REPHEADER command defines the report header; the REPFOOTER command defines the report footer.

A TTITLE, BTITLE, REPHEADER or REPFOOTER

command consists of the command name followed by one or more clauses specifying a position or format and a CHAR value you wish to place in that position or give that format. You can include multiple sets of clauses and CHAR values:

TTITLE position_clause(s) char_value position_clause(s) char_value ...
BTITLE position_clause(s) char_value position_clause(s) char_value ...
REPHEADER position_clause(s) char_value position_clause(s) char_value ...
REPFOOTER position_clause(s) char_value position_clause(s) char_value ...