How to Format Reports with COLUMN in SQL*Plus (BREAK, COMPUTE)

SQL*Plus can turn a plain query into a readable report without changing the SQL. COLUMN sets headings and number formats, BREAK suppresses repeated values and adds blank lines between groups, and COMPUTE adds subtotals per group. SQLcl supports the same commands.

Code for This Guide

The main examples are in the examples/tools folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

column name heading 'Text|Second line' format a12 | 9,990
column name noprint
break on column [skip n]
compute sum | avg | count | min | max label 'Text' of column on break_column
clear columns  |  clear breaks  |  clear computes

A vertical bar in a heading starts a new line. Formats such as a12 set text width, and 9,990 a number pattern.

Headings, Formats, and Groups

Example:

set pagesize 30 linesize 80 feedback off
column airport_code heading 'Code' format a4
column city         heading 'City' format a12
column elevation_ft heading 'Elevation|(ft)' format 9,990
column country_code noprint
break on country_code skip 1
select country_code, airport_code, city, elevation_ft
from   airports
where  country_code in ('IN', 'US')
order  by country_code, airport_code;

Output:

                  Elevation
Code City              (ft)
---- ------------ ---------
BLR  Bengaluru        3,002
BOM  Mumbai              39
DEL  Delhi              777

JFK  New York            13
LAX  Los Angeles        128
ORD  Chicago            672

The columns get short headings, a two-line heading for elevation, and a thousands separator. COUNTRY_CODE is hidden with NOPRINT, but BREAK on it still adds a blank line between India and the United States.

Subtotals with COMPUTE

Example:

set pagesize 40 linesize 80 feedback off
column department_id heading 'Dept' format 999
column last_name format a12
column salary format 99,990
break on department_id skip 1
compute sum label 'Sum' of salary on department_id
select department_id, last_name, salary
from   employees
where  department_id in (50, 60)
order  by department_id, salary desc;

Output:

Dept LAST_NAME     SALARY
---- ------------ -------
  50 Mehta         33,000
     Reddy         15,500
     Williams       8,800
     Schmidt        8,300
****              -------
Sum                65,600

  60 Okafor        29,000
     Costa         11,200
     Mensah         7,400
****              -------
Sum                47,600

BREAK on DEPARTMENT_ID prints each department once, and COMPUTE SUM adds a labeled salary subtotal under each group. The query must be ordered by the break column for the groups to come out right.

Things to Know

• COLUMN settings last for the session; CLEAR COLUMNS resets them.
• COMPUTE works only with a matching BREAK on the same column.
• For results used by other programs, SQLcl's SET SQLFORMAT CSV or JSON fits better than report formatting.

Related Guides

• How to Use Substitution Variables in SQL*Plus
• How to Format Query Output in SQLcl (SET SQLFORMAT)

Conclusion

COLUMN, BREAK, and COMPUTE format SQL*Plus output as reports: headings and formats, grouped rows with spacing, and subtotals. Order the query by the break column, and clear the settings when the report is done.