Skip to Main Content
Oracle in a Nutshell
book

Oracle in a Nutshell

by Rick Greenwald, David C. Kreines
December 2002
Intermediate to advanced content levelIntermediate to advanced
928 pages
85h 29m
English
O'Reilly Media, Inc.
Content preview from 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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Internals

Oracle Internals

Donald K. Burleson
Oracle PL/SQL

Oracle PL/SQL

Lewis Cunningham
Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Rick Greenwald, Robert Stackowiak, Jonathan Stern

Publisher Resources

ISBN: 0596003366Errata Page