SQL* Plus CLI  «Prev  Next»

Lesson 9 Column breaks
Objective Suppress repeating values in a column.

Supress Repeating Values (Column breaks)

SQL*Plus provides several formatting options that improve the readability of your reports. In this lesson, you will learn how to prevent a value from being repeated in successive rows of a report and how to insert blank lines or a page break between groups of records. In the earlier lesson on generating ad-hoc reports, the sample report output looked like this:

OWNER     OBJECT_TYP OBJECT_NAME
--------- ---------- ---------------
DBSNMP    SYNONYM    DBA_DATA_FILES
DBSNMP    SYNONYM    DBA_FREE_SPACE
DBSNMP    SYNONYM    DBA_SEGMENTS
DBSNMP    SYNONYM    DBA_TABLESPACES
OUTLN     INDEX      OL$HNT_NUM
OUTLN     INDEX      OL$NAME
OUTLN     INDEX      OL$SIGNATURE
OUTLN     TABLE      OL$
OUTLN     TABLE      OL$HINTS
PUBLIC    SYNONYM    ALL_ALL_TABLES

In this example, because the report is sorted by owner and then by object type, the values in those columns tend to repeat from one row to the next. You can suppress these repeating values, so that they are printed only once, by adding either of the following commands to the report:

BREAK ON owner NODUPLICATES ON object_type 
NODUPLICATES
BREAK ON owner ON object_type

The keyword NODUPLICATES tells SQL*Plus to print the column's value only when it changes. NODUPLICATES is assumed anytime you issue a BREAK command, so in practice most script writers will leave it off to save themselves some typing. The example here shows both methods, and they are equivalent. The column order in the BREAK command should match that of the query's ORDER BY clause. This becomes especially important when you add page and line breaks to your report.

CLEAR command

The CLEAR command is used to clear settings for the BREAK and COLUMN commands. Settings for these commands remain even after a report has been finished. For example, if your next query contains a column or alias named "title", the 999.99 format is used because this setting was added in the previous script. Therefore, any time you use the COLUMN or BREAK commands in a script, you should always clear the settings at the end of the script so that they don’t affect subsequent reports. Figure 5-9 shows the syntax of the CLEAR command.

CLEAR COLUMN|BREAK
FIGURE 5-9.1: Syntax of the CLEAR command
Figure 5-9.2 shows adding the CLEAR command at the end of the previous script to clear the COLUMN and BREAK settings. Notice that the CLEAR command is issued twice:
  1. once for the BREAK command's settings and
  2. once for the COLUMN command's settings.

After adding the CLEAR command, any settings used for the report are cleared automatically after the report has been displayed.

Figure 5-9.2: CLEAR command added to the end of a script
Figure 5-9.2: CLEAR command added to the end of a script