Formatting Query Results, 2 of 6

Formatting Columns

Through the SQL*Plus COLUMN command, you can change the column headings and reformat the column data in your query results.

Changing Column Headings

When displaying column headings, you can either use the default heading or you can change it using the COLUMN command. The following sections describe how default headings are derived and how to alter them using the COLUMN command. See the COLUMN command in Chapter 8 for more details.

Default Headings

SQL*Plus uses column or expression names as default column headings when displaying query results. Column names are often short and cryptic, however, and expressions can be hard to understand.

Changing Default Headings

You can define a more useful column heading with the HEADING clause of the COLUMN command, in the format shown below:

COLUMN column_name HEADING column_heading

Example 4-1 Changing a Column Heading

To produce a report from EMP_DETAILS_VIEW with new headings specified for LAST_NAME, SALARY, and COMMISSION_PCT, enter the following commands:


COLUMN LAST_NAME        HEADING 'LAST NAME'
COLUMN SALARY           HEADING 'MONTHLY SALARY'
COLUMN COMMISSION_PCT   HEADING COMMISSION
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN'   


LAST_NAME                 MONTHLY SALARY COMMISSION
------------------------- -------------- ----------
Russell                            14000         .4
Partners                           13500         .3
Errazuriz                          12000         .3
Cambrault                          11000         .3
Zlotkey                            10500         .2

Note:

The new headings will remain in effect until you enter different headings, reset each column's format, or exit from SQL*Plus.

To change a column heading to two or more words, enclose the new heading in single or double quotation marks when you enter the COLUMN command. To display a column heading on more than one line, use a vertical bar (|) where you want to begin a new line. (You can use a character other than a vertical bar by changing the setting of the HEADSEP variable of the SET command. See the SET command in Chapter 8 for more information.)

Example 4-2 Splitting a Column Heading

To give the columns SALARY and LAST_NAME the headings MONTHLY SALARY and LAST NAME respectively, and to split the new headings onto two lines, enter


COLUMN SALARY HEADING 'MONTHLY|SALARY'

Now rerun the query with the slash (/) command:


 /



LAST                         MONTHLY
NAME                          SALARY COMMISSION
------------------------- ---------- ----------
Russell                        14000         .4
Partners                       13500         .3
Errazuriz                      12000         .3
Cambrault                      11000         .3
Zlotkey                        10500         .2

To change the character used to underline each column heading, set the UNDERLINE variable of the SET command to the desired character.

Example 4-3 Setting the Underline Character

To change the character used to underline headings to an equal sign and rerun the query, enter the following commands:


SET UNDERLINE =
/


LAST                         MONTHLY
NAME                          SALARY COMMISSION
========================= ========== ==========
Russell                        14000         .4
Partners                       13500         .3
Errazuriz                      12000         .3
Cambrault                      11000         .3
Zlotkey                        10500         .2

Now change the underline character back to a dash:


SET UNDERLINE '-'

Note:

You must enclose the dash in quotation marks; otherwise, SQL*Plus interprets the dash as a hyphen indicating that you wish to continue the command on another line.

Formatting NUMBER Columns

When displaying NUMBER columns, you can either accept the SQL*Plus default display width or you can change it using the COLUMN command. The sections below describe the default display and how you can alter the default with the COLUMN command.

Default Display

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.

SQL*Plus normally displays numbers with as many digits as are required for accuracy, up to a standard display width determined by the value of the NUMWIDTH variable of the SET command (normally 10). If a number is larger than the value of SET NUMWIDTH, SQL*Plus rounds the number up or down to the maximum number of characters allowed.

You can choose a different format for any NUMBER column by using a format model in a COLUMN command. A format model is a representation of the way you want the numbers in the column to appear, using 9s to represent digits.

Changing the Default Display

The COLUMN command identifies the column you want to format and the model you want to use, as shown below:

COLUMN column_name FORMAT model

Use format models to add commas, dollar signs, angle brackets (around negative values), and/or leading zeros to numbers in a given column. You can also round the values to a given number of decimal places, display minus signs to the right of negative values (instead of to the left), and display values in exponential notation.

To use more than one format model for a single column, combine the desired models in one COLUMN command (see Example 4-4). For a complete list of format models and further details, see the COLUMN command in Chapter 8.

Example 4-4 Formatting a NUMBER Column

To display SALARY with a dollar sign, a comma, and the numeral zero instead of a blank for any zero values, enter the following command:


COLUMN SALARY FORMAT $99,990

Now rerun the current query:


/



LAST                       MONTHLY
NAME                        SALARY COMMISSION
------------------------- -------- ----------
Russell                    $14,000         .4
Partners                   $13,500         .3
Errazuriz                  $12,000         .3
Cambrault                  $11,000         .3
Zlotkey                    $10,500         .2

Use a zero in your format model, as shown above, when you use other formats such as a dollar sign and wish to display a zero in place of a blank for zero values.

Note:

The format model will stay in effect until you enter a new one, reset the column's format with

COLUMN column_name CLEAR

or exit from SQL*Plus.

Formatting Datatypes

When displaying datatypes, you can either accept the SQL*Plus default display width or you can change it using the COLUMN command. Datatypes, in this manual, include the following types:

CHAR
NCHAR
VARCHAR2 (VARCHAR)
NVARCHAR2 (NCHAR VARYING)
DATE
LONG
CLOB
NCLOB

Default Display

The default width of datatype columns is the width of the column in the database.

The default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS parameters in effect. Otherwise, the default format width is A9. For more information on formatting DATE columns, see the FORMAT clause of the COLUMN command in Chapter 8.

Left justification is the default for datatypes.

Changing the Default Display

You can change the displayed width of a datatype or DATE, by using the COLUMN command with a format model consisting of the letter A (for alphanumeric) followed by a number representing the width of the column in characters.

Within the COLUMN command, identify the column you want to format and the model you want to use:

COLUMN column_name FORMAT model

If you specify a width shorter than the column heading, SQL*Plus truncates the heading. If you specify a width for a LONG, CLOB, or NCLOB column, SQL*Plus uses the LONGCHUNKSIZE or the specified width, whichever is smaller, as the column width. See the COLUMN command in Chapter 8 for more details.

Example 4-5 Formatting a Character Column

To set the width of the column LAST_NAME to four characters and rerun the current query, enter


COLUMN LAST_NAME FORMAT A4
/


LAST  MONTHLY
NAME   SALARY COMMISSION
---- -------- ----------
Russ  $14,000         .4
ell

Part  $13,500         .3
ners

Erra  $12,000         .3
zuri
z


LAST  MONTHLY
NAME   SALARY COMMISSION
---- -------- ----------
Camb  $11,000         .3
raul
t

Zlot  $10,500         .2
key

Note:

The format model will stay in effect until you enter a new one, reset the column's format with

COLUMN column_name CLEAR

or exit from SQL*Plus.

If the WRAP variable of the SET command is set to ON (its default value), the employee names wrap to the next line after the fourth character, as shown in Example 4-5. If WRAP is set to OFF, the names are truncated (cut off) after the fourth character.

The system variable WRAP controls all columns; you can override the setting of WRAP for a given column through the WRAPPED, WORD_WRAPPED, and TRUNCATED clauses of the COLUMN command. See the COLUMN command in Chapter 8 for more information on these clauses. You will use the WORD_WRAPPED clause of COLUMN later in this chapter.

Note:

The column heading is truncated regardless of the setting of WRAP or any COLUMN command clauses.

Now return the column to its previous format:


COLUMN LAST_NAME FORMAT A10

Copying Column Display Attributes

When you want to give more than one column the same display attributes, you can reduce the length of the commands you must enter by using the LIKE clause of the COLUMN command. The LIKE clause tells SQL*Plus to copy the display attributes of a previously defined column to the new column, except for changes made by other clauses in the same command.

Example 4-6 Copying a Column's Display Attributes

To give the column COMMISSION_PCT the same display attributes you gave to SALARY, but to specify a different heading, enter the following command:


COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS

Rerun the query:

/


LAST        MONTHLY
NAME         SALARY    BONUS
---------- -------- --------
Russell     $14,000       $0
Partners    $13,500       $0
Errazuriz   $12,000       $0
Cambrault   $11,000       $0
Zlotkey     $10,500       $0

Listing and Resetting Column Display Attributes

To list the current display attributes for a given column, use the COLUMN command followed by the column name only, as shown below:


COLUMN column_name

To list the current display attributes for all columns, enter the COLUMN command with no column names or clauses after it:


COLUMN

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


COLUMN column_name CLEAR

To reset the attributes for all columns, use the COLUMNS clause of the CLEAR command.

Example 4-7 Resetting Column Display Attributes to their Defaults

To reset all columns' display attributes to their default values, enter the following command:


CLEAR COLUMNS


columns cleared

Suppressing and Restoring Column Display Attributes

You can suppress and restore the display attributes you have given a specific column. To suppress a column's display attributes, enter a COLUMN command in the following form:

COLUMN column_name OFF

The OFF clause tells SQL*Plus to use the default display attributes for the column, but does not remove the attributes you have defined through the COLUMN command. 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

As you have seen, by default SQL*Plus wraps column values to additional lines when the value does not fit the column width. If you want to insert a record separator (a line of characters or a blank line) after each wrapped line of output (or after every row), use the RECSEP and RECSEPCHAR variables of the SET command.

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, and to OFF to suppress printing. The default setting of RECSEP is WRAPPED.

RECSEPCHAR sets the character printed in each line. You can set RECSEPCHAR to any character.

You may wish to wrap whole words to additional lines when a column value wraps to additional lines. To do so, use the WORD_WRAPPED clause of the COLUMN command as shown below:

COLUMN column_name WORD_WRAPPED

Example 4-8 Printing a Line of Characters after Wrapped Column Values

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


SET RECSEP WRAPPED
SET RECSEPCHAR "-"

Finally, enter the following query:


SELECT LAST_NAME, JOB_TITLE, CITY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;

Now restrict the width of the column JOB_TITLE and tell SQL*Plus to wrap whole words to additional lines when necessary:


COLUMN JOB_TITLE FORMAT A20 WORD_WRAPPED

Run the query:


/


LAST_NAME                 JOB_TITLE            CITY
------------------------- -------------------- ------------------------------
King                      President            Seattle
Kochhar                   Administration Vice  Seattle
                          President
--------------------------------------------------------------------------------
De Haan                   Administration Vice  Seattle
                          President
--------------------------------------------------------------------------------
Russell                   Sales Manager        Oxford
Partners                  Sales Manager        Oxford
Hartstein                 Marketing Manager    Toronto

6 rows selected.If you set RECSEP to EACH, SQL*Plus prints a line of characters after every row (after every department, for the above example).

Before continuing, set RECSEP to OFF to suppress the printing of record separators:


SET RECSEP OFF