Formatting SQL*Plus Reports

Sometimes you may find difficulties while displaying result on SQL*Plus like a column length displayed as a long length and thus does not result a fine output. In this post I will try to show how we can make good looking result displayed on SQL*Plus.

With COLUMN a column can be formatted. For string type data format is specified by A and then length. Like A7 means total column will span to 7 word length. For number data type the format is 99999 where number of 9s decide the length of digits of number data type.
Changing Column Headings
To change the heading of column a,b and c use commands like below. The vertical bar is used if you want to display the columns in a new line.

SQL> COLUMN A HEADING 'FIRST|COLUMN'
SQL> COLUMN C HEADING 'THIRD|COLUMN'
SQL> COLUMN B HEADING 'SECOND|COLUMN' FORMAT A7
SQL> select a,b,c from test1;

    FIRST SECOND  THIRD
   COLUMN COLUMN  COLUMN
---------- ------- ------
        1 Hi      Hi2

Setting the Underline Character
You see the underline character in the above output under heading is set to -. If you want to change it to = then use,

SQL> SET UNDERLINE =
SQL> /

    FIRST SECOND  THIRD
   COLUMN COLUMN  COLUMN
========== ======= ======
        1 Hi      Hi2

Default Display of columns
1)A NUMBER column's width equals the width of the heading or the width of the FORMAT plus one space for the sign, whichever is greater. If you do not explicitly use FORMAT, then the column's width will always be at least the value of SET NUMWIDTH.

BY default the NUMWIDTH is set to 10.

SQL> select 22/7 "This is the pi value" from dual;

This is the pi value
--------------------
         3.14285714

SQL> show numwidth
numwidth 10

2)The default width of datatype columns is the width of the column in the database. The column width of a LONG, CLOB, NCLOB or XMLType defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever is the smaller.

If LONG is set to 80 and LONGCHUNKSIZE is set to 90 then for CLOB database only first 80 character will be shown on Sql*plus by default.

3)The default width and format of unformatted DATE columns in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter. Otherwise, the default format width is A9.

Listing and Resetting Column Display Attributes
1)To list the current display attributes for a given column, use the COLUMN command followed by the column name.

COLUMN column_name

2)To list the current display attributes for all columns, enter just the COLUMN command.

COLUMN

3)To reset the display attributes for a column to their default values, use the CLEAR clause of the COLUMN command as below.

COLUMN column_name CLEAR

4)To reset all column display attributes to their default values enter,

CLEAR COLUMNS

Suppressing and Restoring Column Display Attributes
Though COLUMN command you once format a column and now you want to use the default display attributes for the column, but you don't want to remove the attributes you have defined through the COLUMN command. You can achieve that by,
COLUMN column_name OFF

To restore the attributes you defined through COLUMN, use the ON clause:

COLUMN column_name ON

Printing a Line of Characters after Wrapped Column Values
RECSEP determines when the line of characters is printed;
-you set RECSEP to EACH to print after every line,
-to WRAPPED to print after wrapped lines,
-The default setting of RECSEP is WRAPPED.

RECSEPCHAR sets the character printed in each line.

To print a line of dashes after each wrapped column value, enter:

SET RECSEP WRAPPED
SET RECSEPCHAR "-"

Clarifying Your Report with Spacing and Summary Lines
With the BREAK command you can suppress the duplicate values in a column specified in an ORDER BY clause. If two columns values come in the subsequent rows and you use BREAK on the column then only one will be shown as below.

Note that here I did not use order by clause. You should use order by on the column that you break. If you do not do this, breaks occur every time the column value changes.

SQL> select station_id,uname from users where station_id!=1 and rownum<=10;

STATION_ID UNAME
---------- --------------------------------------------------
        4 a.sadat
       14 parvez
       14 reza
       14 voyeger
       40 support
       71 accounts5
       71 bill5
       71 chinmoy
       71 crash5
       71 mubeen5

10 rows selected.

SQL> break on station_id skip 2
SQL> select station_id,uname from users where station_id!=1 and rownum<=10;

STATION_ID UNAME
---------- --------------------------------------------------
        4 a.sadat


       14 parvez
          reza
          voyeger


       40 support


       71 accounts5
          bill5
          chinmoy
          crash5
          mubeen5



10 rows selected.

SQL> break on station_id skip page
SQL> /

STATION_ID UNAME
---------- --------------------------------------------------
        4 a.sadat

STATION_ID UNAME
---------- --------------------------------------------------
       14 parvez
          reza
          voyeger

STATION_ID UNAME
---------- --------------------------------------------------
       40 support

STATION_ID UNAME
---------- --------------------------------------------------
       71 accounts5
          bill5
          chinmoy
          crash5
          mubeen5

10 rows selected.

To insert n blank lines, use the BREAK command in the following form:

BREAK ON break_column SKIP n

where n can be 1 or 2 or ....
To skip a page, use the command in this form:

BREAK ON break_column SKIP PAGE

You may wish to insert blank lines or a blank page after every row.
To skip n lines after every row, use BREAK in the following form:

BREAK ON ROW SKIP n

To skip a page after every row, use

BREAK ON ROW SKIP PAGE

You can list your current break definition by entering the BREAK command with no clauses:

BREAK

You can remove the current break definition by entering the CLEAR command with the BREAKS clause:

CLEAR BREAKS