Adding Line and Page Breaks to a SQL*Plus Report

The BREAK command, already used in Lesson 9 to suppress repeating values, can also insert visual space into a report: a blank line when a column's value changes, or something closer to a page break. The mechanism for both is the same SKIP clause, added directly onto a BREAK command:

BREAK
ON column_name SKIP {PAGE|line_count}
ON column_name SKIP {PAGE|line_count}
...

ON column_name identifies which column the following SKIP clause applies to. SKIP PAGE produces a page break whenever that column's value changes; a numeric line_count instead advances that many lines. You can list as many ON clauses as you need in a single BREAK command, one per column you want to react to.

Suppose you want the DBA_OBJECTS report to print each owner on its own page, and skip a single blank line every time the object type changes within an owner's section. That is exactly what this command does:

BREAK ON owner SKIP PAGE ON object_type SKIP 1

SKIP PAGE after owner starts a new page whenever the owner column's value changes. SKIP 1 after object_type inserts one blank line whenever the object type changes within that owner's rows. Combined with the formatting from earlier lessons, the complete script looks like this:

BREAK ON owner SKIP PAGE ON object_type SKIP 1
COLUMN owner FORMAT A12
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 looks like this:

OWNER        OBJECT_TYP OBJECT_NAME
------------ ---------- ----------------
DBSNMP       SYNONYM    DBA_DATA_FILES
                        DBA_FREE_SPACE
                        DBA_SEGMENTS
                        DBA_TABLESPACES

OWNER        OBJECT_TYP OBJECT_NAME
------------ ---------- ----------------
OUTLN        INDEX      OL$HNT_NUM
                        OL$NAME
                        OL$SIGNATURE

                        TABLE      OL$
                        OL$HINTS

Notice the header reprints entirely when owner changes from DBSNMP to OUTLN, and a single blank line appears where object_type shifts from INDEX to TABLE within the OUTLN section, exactly matching the two SKIP clauses in the BREAK command.

SQL*Plus does not produce a page break the way you might expect from that header reprint. With default settings, what actually happens is a blank-line gap followed by the column headers printing again, which is easy enough to follow on screen but will not start an actual new sheet of paper if you send the report to a printer. PAGESIZE, set with the SET command, only controls how many lines SQL*Plus considers to make up one page; it does not by itself insert a physical form-feed character. If you genuinely need a printed page break, you need SET NEWPAGE 0 in addition to SKIP PAGE; without it, SKIP PAGE only ever produces the header-reprint behavior shown above, regardless of how large or small PAGESIZE is set.

A handful of BREAK behaviors are easy to get wrong the first time you use SKIP in a real report. If there is a break after the very last row of data in a report, SQL*Plus does not skip a page there at all, which is also why BREAK ON REPORT SKIP PAGE is not a valid combination: REPORT marks the end of the report, and there is no next page to skip to. Relatedly, SKIP n does not work while SET MARKUP HTML ON is active unless you have also set PREFORMAT ON, since HTML output handles line spacing through markup rather than raw line breaks.

Two special break targets are worth knowing alongside ordinary columns: ON ROW and ON REPORT. ON ROW takes effect on every returned row, and it always becomes the innermost break no matter where you place it among your other BREAK clauses. ON REPORT, by contrast, always becomes the outermost break regardless of where you write it, and it exists specifically to mark a place in the report for a COMPUTE command, most commonly to print a grand total across the entire report rather than a subtotal within one group.

One habit worth building early: BREAK does not accumulate settings across multiple commands. Each new BREAK command you issue completely replaces whatever BREAK definition was active before it, rather than adding to it. If you want to see exactly what break definition is currently active in your session, enter BREAK by itself with no arguments, and SQL*Plus lists it for you. To remove a break definition entirely, use CLEAR BREAKS, the same command covered in Lesson 9 for clearing settings between reports.

Between the header-reprint behavior covered here and the NEWPAGE setting for genuine printed page breaks, you now have the full picture of how SQL*Plus handles page-level formatting: what happens by default, why it is not a true page break, and exactly what additional setting turns it into one.