Oracle in a Nutshell

BREAK command

To eliminate repetitive column values, use the BREAK command as shown in this example:

SQL> BREAK ON owner

SQL> SELECT owner, table_name
  2  FROM all_tables
  3  ORDER BY owner, table_name;

OWNER      TABLE_NAME
========== ===============
CTXSYS     DR$CLASS
           DR$DELETE
           DR$INDEX
DEMO       CUSTOMER
           DEPARTMENT
           EMPLOYEE

When you list a column in the BREAK command, SQL*Plus prints the value of the column only when it changes. For this to work properly, remember to sort the query results on the same column.

You can also use the BREAK command to skip lines or skip to a new page whenever a value changes. For example:

BREAK ON owner SKIP 1
BREAK ON owner SKIP PAGE

The first command results in printing a blank line whenever the owner changes. The second results in a page break each time the owner changes.

Multiple breaks may be specified for a report, but with the same command. The following example causes a page break to occur whenever an owner changes and a blank line to be printed whenever the object type changes:

BREAK ON owner SKIP PAGE ON object_type SKIP 1
SELECT owner, object_type, object_name
   FROM dba_objects
ORDER BY owner, object_type, object_name;

Before performing the break actions for a column, SQL*Plus will first perform the break actions for all inner columns. In this case, a change in the owner field would result in one skipped line and then a page break.