SQL Extensions   «Prev  Next»

Lesson 7 Environment Settings for Reports
Objective Define the environment settings for SQL*Plus reports.

Environment Settings for SQL*Plus Reports

Up to this point, you have used SQL*Plus to run queries and adjust column attributes. In this lesson, you will learn how to define report-level environment settings so that your output looks like a professional report instead of a raw result set. These settings allow you to:

All of these behaviors are driven by SQL*Plus commands that wrap around your SELECT statement and stay in effect for the duration of your session or until you turn them off.

Core SQL*Plus Report Environment Commands

The commands below are specific to SQL*Plus and are used to define page layout, report headers and footers, and group-level calculations.

Command Description
TTI[TLE] spec… | OFF | ON Defines the title that appears at the top of each page of the report. The title can include:
  • Positioning: LEFT, CENTER, RIGHT
  • Layout: SKIP n (skip n lines), COL n (start at column n)
  • Formatting: BOLD
  • Variables: for example, SQL.PNO for the current page number
Use TTITLE OFF to turn the title off and TTITLE ON to re-enable the last definition.
BTI[TLE] spec… | OFF | ON Defines a footer that appears at the bottom of each page. Uses the same syntax and formatting options as TTITLE.
BRE[AK] Controls where SQL*Plus breaks the report and what it does at each break point. Typical syntax:
BREAK ON {column | ROW | REPORT} [SKIP n | SKIP PAGE] [DUPLICATES | NODUPLICATES]
  • ON column: start a new group whenever the column value changes.
  • SKIP n: skip n lines before printing the next row or group.
  • NODUPLICATES (default): do not repeat the group value on each line, only at the start of the group.
BREAK integrates closely with COMPUTE to print group totals.
COMP[UTE] Computes summary values (group functions) at break points or at the end of the report. General syntax:
COMPUTE function [LABEL text] OF column ON {column | ROW | REPORT}
  • Group functions: SUM, AVG, MIN, MAX, COUNT, STD, VAR.
  • ON column: print the computed value when the BREAK on that column fires.
  • ON REPORT: print a grand total or other summary at the end of the report.
REPF[OOTER] [PAGE] spec… | OFF | ON Defines a report footer that prints once at the end of the report or at the end of each page (when PAGE is used). Syntax and formatting options match TTITLE.
REPH[EADER] [PAGE] spec… | OFF | ON Defines a report header that prints once at the beginning of the report or at the beginning of each page (when PAGE is used). Syntax and formatting options match TTITLE.

In practice, you will normally combine these commands with COLUMN and SET statements from the previous lessons to create a complete report layout.

Oracle Database SQL

Example: Customer Purchasing Report with Group Totals

The following example (based on the transcribed image) shows how TTITLE, BREAK, and COMPUTE work together to produce a customer purchasing report that groups sales by last name and prints a total for each customer.

SQL*Plus report that computes SUM of TOTAL_SALE by LASTNAME with TTITLE, BREAK, and COMPUTE 

SQL> TTITLE LEFT "Customer Purchasing" -
  2        SKIP 1 "Pg." SQL.PNO SKIP 2

SQL> COMPUTE SUM OF TOTAL_SALE ON LASTNAME

SQL> BREAK ON LASTNAME SKIP 1

SQL> SELECT
  2         C.LASTNAME,
  3         CS.SALES_DATE,
  4         CS.TOTAL_SALE_AMOUNT TOTAL_SALE
  5  FROM   CUSTOMER       C,
  6         CUSTOMER_SALE  CS
  7  WHERE  C.CUST_ID = CS.CUST_ID
  8  ORDER  BY C.LASTNAME, CS.SALES_DATE;
Customer Purchasing 

Pg.          1

LASTNAME                  SALES_DATE   TOTAL_SALE
------------------------  ----------   ----------
Black                     12-DEC-99         61.90
                          01-MAR-00        108.03
************************
sum                                       169.93

Lee                       14-JUL-99         52.66
  1. TTITLE defines a two-line page title. The dash (-) at the end of the first line continues the command on the next line.
  2. LEFT "Customer Purchasing" places the title at the left edge of the first line.
  3. SKIP 1 "Pg." SQL.PNO SKIP 2 prints Pg. followed by the current page number (using the predefined SQL.PNO variable), then skips two lines before the column headings.
  4. COMPUTE SUM OF TOTAL_SALE ON LASTNAME instructs SQL*Plus to compute a sum of the TOTAL_SALE column for each group of rows sharing the same LASTNAME.
  5. BREAK ON LASTNAME SKIP 1 defines a break at each change in LASTNAME, suppresses duplicate printing of the last name within the group, and skips one line after the group total line.
  6. The SELECT statement is ordered by LASTNAME and SALES_DATE so that related rows appear together and totals are correctly computed for each customer.
  7. When the LASTNAME value changes (for example, from Black to Lee), SQL*Plus prints the row group, displays the group sum (marked by a line of asterisks), then skips a line before printing the next customer's group.
This pattern—TTITLE for page title, BREAK for grouping, and COMPUTE for totals—is the foundation for many classic SQL*Plus report layouts.

Matching Report Commands to Their Roles

When designing a report, it helps to think of each SQL*Plus environment command in terms of the part of the report it controls:

  1. TTITLE – Defines a page title printed once per page at the top.
  2. BTITLE – Defines a page footer printed once per page at the bottom.
  3. REPH EADER – Defines a header printed once per report (or per page if PAGE is used).
  4. REPF OOTER – Defines a footer printed once at the end of the report (or per page if PAGE is used).
  5. COLUMN – Controls the formatting and attributes of individual columns in the SELECT list (headings, width, alignment).
  6. BREAK – Defines where to group rows, when to skip lines, and whether to suppress duplicate values.
  7. COMPUTE – Defines which aggregate functions to print at each break or at the end of the report.
  8. SET – Controls global environment parameters such as PAGESIZE and LINESIZE, which determine page length and line width.

Syntax Pattern for Titles and Report Headers/Footers

All title and header/footer commands share a common syntax pattern: a command name followed by one or more pairs of positioning/formatting clauses and character 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 ...

Examples of position_clause values include:

By combining these clauses, you can construct detailed page headers and footers that include report names, dates, page numbers, and even dynamic values from SQL*Plus substitution variables.

In the next lesson, you will build on these settings by learning how to spool report output to files so that formatted SQL*Plus reports can be archived, printed, or shared with other users.


SEMrush Software 7 SEMrush Banner 7