SQL Extensions   «Prev  Next»

Lesson 7Environment Settings for Reports
ObjectiveDefine 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:
  1. TTITLE,
  2. BREAK, and
  3. COMPUTE
work together.
  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.
  2. 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.
  3. The BREAK command here says that the report will break on each new value of the LASTNAME column and then skip one line.
  4. This query is executed.
  5. 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).
  6. This is the beginning of the second line of the report title. The TTITLE command (second line of the command) stated to print "Pg."
  7. 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.
  8. This blank line is here because the TTITLE says "SKIP 2" at the end of the command.
  9. 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).
  10. 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