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 HeadingsTo 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 CharacterYou 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 columns1)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 Attributes1)To list the current display attributes for a given column, use the COLUMN command followed by the column name.
COLUMN column_name2)To list the current display attributes for all columns, enter just the COLUMN command.
COLUMN3)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 CLEAR4)To reset all column display attributes to their default values enter,
CLEAR COLUMNS
Suppressing and Restoring Column Display AttributesThough 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 OFFTo restore the attributes you defined through COLUMN, use the ON clause:
COLUMN column_name ONPrinting a Line of Characters after Wrapped Column ValuesRECSEP 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 LinesWith 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 nwhere n can be 1 or 2 or ....
To skip a page, use the command in this form:
BREAK ON break_column SKIP PAGEYou 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 nTo skip a page after every row, use
BREAK ON ROW SKIP PAGEYou can list your current break definition by entering the BREAK command with no clauses:
BREAKYou can remove the current break definition by entering the CLEAR command with the BREAKS clause:
CLEAR BREAKS
