SQL*Plus
User's Guide and Reference
Release 8.1.6 A75664-01 |
|
Formatting Query Results, 2 of 6
Through the SQL*Plus COLUMN command, you can change the column
headings and reformat the column data in your query results.
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.
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.
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 in
Chapter 8 for more details.
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 the SET command
in Chapter 8 for more information.)
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.
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 that you wish to continue the command on another line. |
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.
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.
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.
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. |
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 variables:
NCLOB
Note: The NCHAR, NVARCHAR2 (NCHAR VARYING), CLOB and NCLOB datatypes require Oracle8 or higher. |
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.
Note: The default justification for datatypes is left justification. |
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.
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. |
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:
SQL> COLUMN ENAME FORMAT A10
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.
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
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.
To reset all columns' display attributes to their default values, enter the following command:
SQL> CLEAR COLUMNS columns cleared
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
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
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
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|