SQL*Plus User's Guide and Reference

Contents Index Home Previous Next

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 sections below describe how the default headings are derived and how you can alter them with the COLUMN command.

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

See the COLUMN command[*] for more details.

Example 4-1 Changing a Column Heading

To produce a report from EMP with new headings specified for DEPTNO, ENAME, and SAL, enter the following commands:

SQL> COLUMN DEPTNO HEADING Department
SQL> COLUMN ENAME HEADING Employee
SQL> COLUMN SAL HEADING Salary
SQL> COLUMN COMM HEADING Commission
SQL> SELECT DEPTNO, ENAME, SAL, COMM
  2  FROM EMP
  3  WHERE JOB = 'SALESMAN';

SQL*Plus displays the following output:

Department Employee     Salary Commission
---------- ---------- ---------- ----------
        30 ALLEN            1600        300
        30 WARD             1250        500
        30 MARTIN           1250       1400
        30 TURNER           1500          0

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 SET[*] for more information.)

Example 4-2 Splitting a Column Heading

To give the column ENAME the heading EMPLOYEE NAME and to split the new heading onto two lines, enter

SQL> COLUMN ENAME HEADING 'Employee|Name'

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

SQL> /

SQL*Plus displays the following output:

           Employee
Department Name           Salary Commission
---------- ---------- ---------- ----------
        30 ALLEN            1600        300
        30 WARD             1250        500
        30 MARTIN           1250       1400
        30 TURNER           1500          0

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:

SQL> SET UNDERLINE =
SQL> /

SQL*Plus displays the following results:

           Employee
Department Name           Salary Commission
========== ========== ========== ==========
        30 ALLEN            1600        300
        30 WARD             1250        500
        30 MARTIN           1250       1400
        30 TURNER           1500          0

Now change the underline character back to a dash:

SQL> SET UNDERLINE '-'

Note: You must enclose the dash in quotation marks; otherwise, SQL*Plus interprets the dash as a hyphen indicating 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 9's 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[*].

Example 4-4 Formatting a NUMBER Column

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

SQL> COLUMN SAL FORMAT $99,990

Now rerun the current query:

SQL> /

SQL*Plus displays the following output:

           Employee
Department Name           Salary Commission
---------- ---------- ---------- ----------
       30  ALLEN         $1,600         300
       30  WARD          $1,250         500
       30  MARTIN        $1,250        1400
       30  TURNER        $1,500           0

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, or exit from SQL*Plus.

Formatting CHAR, VARCHAR2 (VARCHAR), LONG, DATE, and Trusted Oracle Columns

When displaying CHAR, VARCHAR2 (VARCHAR), LONG, DATE, and Trusted Oracle 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 defaults and how you can alter the defaults with the COLUMN command.

Default Display

The default width of CHAR and VARCHAR2 (VARCHAR) columns is the width of the column in the database. (VARCHAR2 requires Oracle7.)

The display width of LONG columns defaults to the value of the LONGCHUNKSIZE variable of the SET command.

For Oracle7, 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. With Oracle Version 6, the default width for DATE columns is nine characters. For more information on formatting DATE columns, see the FORMAT clause of the COLUMN command[*].

The default display width for the Trusted Oracle datatypes MLSLABEL and RAW MLSLABEL is the width defined for the column in the database or the width of the column heading, whichever is longer. (Note that the default display width for a Trusted Oracle column named ROWLABEL is 15.)

Note: The default justification for CHAR, VARCHAR2 (VARCHAR), LONG, DATE, and Trusted Oracle columns is left justification.

Changing the Default Display

You can change the displayed width of a CHAR, VARCHAR2 (VARCHAR), LONG, DATE, or Trusted Oracle column 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 column, SQL*Plus uses the LONGCHUNKSIZE or the specified width, whichever is smaller, as the column width. See the COLUMN command[*] for more details.

Example 4-5 Formatting a Character Column

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

SQL> COLUMN ENAME FORMAT A4
SQL> /

SQL*Plus displays the results:

           Empl
Department Name     Salary Commission
---------- ---- ---------- ----------
       30  ALLE     $1,600        300
           N

       30  WARD     $1,250        500
       30  MART     $1,250       1400
           IN

       30  TURN     $1,500          0
           ER

Note: The format model will stay in effect until you enter a new one, reset the column's format, or exit from SQL*Plus. ENAME could be a CHAR or VARCHAR2 (VARCHAR) column.

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 COLUMN[*] 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:

SQL> COLUMN ENAME 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 COMM the same display attributes you gave to SAL, but to specify a different heading, enter the following command:

SQL> COLUMN COMM LIKE SAL HEADING Bonus

Rerun the query:

SQL> /

SQL*Plus displays the following output:

           Employee
Department Name           Salary      Bonus
---------- ---------- ---------- ----------
        30 ALLEN          $1,600       $300
        30 WARD           $1,250       $500
        30 MARTIN         $1,250     $1,400
        30 TURNER         $1,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:

SQL> CLEAR COLUMNS
columns cleared

You may wish to place the command CLEAR COLUMNS at the beginning of every command file to ensure that previously entered COLUMN commands will not affect queries you run in a given file.

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 within 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 following commands:

SQL> SET RECSEP WRAPPED
SQL> SET RECSEPCHAR '-'

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

SQL> COLUMN LOC FORMAT A7 WORD_WRAPPED

Finally, enter and run the following query:

SQL> SELECT * FROM DEPT;

SQL*Plus displays the results:

    DEPTNO DNAME           LOC
---------- --------------- ----------
        10 ACCOUNTING      NEW
                           YORK
-------------------------------------------------
        20 RESEARCH        DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON

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:

SQL> SET RECSEP OFF


Contents Index Home Previous Next