COLUMN column_name HEADING column_heading
See the COLUMN command![[*]](jump.gif)
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 0Note: 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 0To 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 0Now 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.
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.
COLUMN column_name FORMAT model
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 0Use 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.
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.
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
ERNote: 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
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
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.
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
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 BOSTONIf 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