SQL* Plus CLI  «Prev  Next»

Lesson 10Line and page breaks
ObjectiveAdd line breaks and page breaks to a report

Line and Page Breaks (Oracle Report)

The BREAK command also may be used to skip lines when a column's value changes. By adding a skip clause, it also can advance to a new page when a column's value changes. The format for this skip clause is shown in the following diagram:
Diagram describing the skip clause
  1. BREAK: The SKIP clause is part of the BREAK command.
  2. ON column_name: This tells SQL*Plus which column you are working with. The subsequent SKIP clause applies to this column.
  3. SKIP: The SKIP keyword introduces the skip clause.
  4. PAGE: If you use SKIP PAGE, the result is a page-break whenever the column's value changes.
  5. line_count: If you do not want a page-break, then specify a number here. When the column value changes, SQL*Plus will advance the specified number of lines.
  6. The entire line containing the second "ON" clause: You may list as many columns as you like in the BREAK command.

If, for example, you wanted the report showing database objects to print each owner's own set of pages, skipping one line each time the object type changed, you would use the following BREAK command:

BREAK ON owner SKIP PAGE ON object_type SKIP 1

The SKIP PAGE following the owner column tells SQL*Plus to start a new page when the owner column changes.
The SKIP 1 following the object_type column tells SQL*Plus to skip one line when the object_type column changes
The final script for the object report looks like this:

BREAK ON owner SKIP PAGE ON object_type SKIP 1
COLUMN object_type FORMAT A10
COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name
FROM dba_objects
ORDER BY owner, object_type, object_name;

The output will look like this:
------------ ---------- ----------------
------------ ---------- ----------------
OUTLN          INDEX      OL$HNT_NUM
                    TABLE      OL$

SQL*Plus does not create page breaks as you would expect. With the default settings, SQL*Plus skips one blank line and reprints the page titles. If you lookat the report on the screen, it is fairly easy to follow. However, if you plan to print the report, you may want a form-feed character to start each new page. Later in this module, you will learn how to use the NEWPAGE setting to do that.