RelationalDBDesign RelationalDBDesign


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 mouseover:
  1. The SKIP clause is part of the BREAK command.
  2. This tells SQL*Plus which column you are working with. Thesubsequent SKIP clause applies to this column.
  3. The SKIP keyword introduces the skip clause.
  4. If you use SKIP PAGE, the result is a page-break whenever the column's value changes.
  5. 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. You may list as many columns as you like in the BREAK command.
The SKIP clause is part of the BREAK command.

SQL Loader Control File
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 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 will look 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

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.