| 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|BREAKFIGURE 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:
After adding the CLEAR command, any settings used for the report are cleared automatically after the report has been displayed.
- once for the BREAK command's settings and
- 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.
