13.14 COLUMN
Syntax
COL[UMN] [{column | expr} [option ...]]where option represents one of the following clauses:
ALI[AS] alias BOOL[EAN]{YES [NO]} CLE[AR] ENTMAP {ON | OFF} FOLD_A[FTER] FOLD_B[EFORE] FOR[MAT] format HEA[DING] text JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]} LIKE {expr | alias} NEWL[INE] NEW_V[ALUE] variable NOPRI[NT] | PRI[NT] NUL[L] text OLD_V[ALUE] variable ON | OFF WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
Specifies display attributes for a given column, such as
-
Text for the column heading
-
Format for
NUMBERdata -
Wrapping of column data
-
Change the default
BOOLEANvalue
It also lists the current display attributes for a single column or for all columns.
Enter COLUMN followed by column or expr and no other clauses to list the current display attributes for only the specified column or expression. Enter COLUMN with no clauses to list all current column display attributes.
Terms
{column | expr}Identifies the data item (typically the name of a column) in a SQL
SELECT command to which the
column command refers. If you use an expression in a COLUMN
command, you must enter expr exactly as it appears in the
SELECT command. If the expression in the
SELECT command is a+b, for example, you cannot use b+a or (a+b)
in a COLUMN command to refer to the expression in the
SELECT command.
If you select columns with
the same name from different tables, a COLUMN command for that
column name will apply to both columns. That is, a COLUMN command
for the column LAST_NAME applies to all columns named
LAST_NAME that you reference in this session.
COLUMN ignores table name prefixes in SELECT
commands. Also, spaces are ignored unless the name is placed in double quotes.
To format the columns differently, assign a unique alias to each column
within the SELECT command itself (do not use the
ALIAS clause of the COLUMN command) and enter
a COLUMN command for each column's
alias.
ALI[AS] aliasAssigns a
specified
alias to a column, which can be used to refer to the column in
BREAK, COMPUTE, and other
COLUMN commands.
CLE[AR]
Resets the display attributes for the column to default values.
To
reset the attributes for all columns, use the CLEAR COLUMNS
command. CLEAR COLUMNS also clears the
ATTRIBUTEs for that
column.
ENTMAP {ON | OFF}Enables entity mapping
to be turned ON or OFF for selected columns in
HTML output. This feature enables you to include, for example, HTML hyperlinks in a
column of data, while still mapping entities in other columns of the same report. By
turning entity mapping OFF for a column containing HTML hyperlinks,
the HTML anchor tag delimiters, <, >,
", and, & are correctly interpreted in the
report. Otherwise, they would be replaced with their respective entities,
<, >,
" and &, preventing web
browsers from correctly interpreting the HTML.
Entities in the
column heading and any COMPUTE labels or output appearing in the
column are mapped or not mapped according to the value of ENTMAP
for the column.
The default setting for COLUMN
ENTMAP is the current setting of the MARKUP HTML
ENTMAP option.
For more
information about the MARKUP HTML ENTMAP option, see
SET
MARKUP
Options.
FOLD_A[FTER]
Inserts a carriage
return after the column heading and after each row in the column. SQL*Plus does not
insert an extra carriage return after the last column in the SELECT
list. FOLD_A[FTER] does not work in SET MARKUP HTML
ON mode unless PREFORMAT is set
ON.
FOLD_B[EFORE]
Inserts a carriage
return before the column heading and before each row of the column. SQL*Plus does
not insert an extra carriage return before the first column in the
SELECT list. FOLD_A[FTER] does not work in
SET MARKUP HTML ON mode unless PREFORMAT is
set ON.
FOR[MAT] formatSpecifies the display format of the column. The format specification must be a text constant, such as A10 or $9,999.
Character Columns
The default
width of CHAR, NCHAR,
VARCHAR2
(VARCHAR), and NVARCHAR2
(NCHAR VARYING) columns is the width of the column in the
database. SQL*Plus formats these data types left-justified. If a value does not fit
within the column width, SQL*Plus wraps or truncates the character string, depending
on the setting of SET WRAP.
A
LONG, BLOB, BFILE,
CLOB, NCLOB, XMLType, or
JSON column's width defaults to the value of
SET
LONGCHUNKSIZE or SET LONG,
whichever is smaller.
To change the width of a data type to n,
use FORMAT An. (A stands for alphabetic.) If you specify a
width shorter than the column heading, SQL*Plus truncates the heading.
SQL*Plus truncates or wraps XMLType columns after
2000 bytes. To avoid this, you need to set an explicit COLUMN
format for the XMLType column. A COLUMN format can
be up to a maximum of 60,000 per row.
DATE Columns
The default width and format of unformatted DATE
columns in SQL*Plus are derived from the NLS_DATE_FORMAT parameter.
The NLS_DATE_FORMAT setting is determined by the NLS territory
parameter. For example, the default format for the NLS territory, America, is
DD-Mon-RR, and the default width is A9. The
NLS parameters may be set in your database parameter file, in environment variables,
or an equivalent platform-specific mechanism. They may also be specified for each
session with the ALTER SESSION command. For more information about
DATE formats and about NLS parameters, see the Oracle
Database SQL Language Reference.
You can
change the format of the DATE column
using the SQL function TO_CHAR in your SQL SELECT
statement. You may also wish to use an explicit COLUMN FORMAT
command to adjust the column width.
When you use SQL functions
like TO_CHAR, Oracle Database automatically enables a very wide
column. The default column width may also depend on the character sets in use in
SQL*Plus and in the database. To maximize script portability if multiple character
sets are used, Oracle Database recommends using COLUMN FORMAT for
each column selected.
To change the width of a
DATE column to n, use the COLUMN
command with FORMAT An. If you specify a width shorter than
the column heading, the heading is truncated.
NUMBER Columns
For numeric columns, COLUMN
FORMAT settings take precedence over SET NUMFORMAT
settings, which take precedence over SET NUMWIDTH settings.
See SET NUMF[ORMAT] format and SET NUM[WIDTH] {10 | n}.
To change a NUMBER column's
width, use FORMAT followed by an element
as specified in Table 13-1.
Table 13-1 Number Formats
| Element | Examples | Description |
|---|---|---|
|
, (comma) |
9,999 |
Displays a comma in the specified position. |
|
. (period) |
99.99 |
Displays a period (decimal point) to separate the integral and fractional parts of a number. |
|
$ |
$9999 |
Displays a leading dollar sign. |
|
0 |
0999 9990 |
Displays leading zeros Displays trailing zeros. |
|
9 |
9999 |
Displays a value with the number of digits specified by the number of 9s. Value has a leading space if positive, a leading minus sign if negative. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero. |
|
B |
B9999 |
Displays blanks for the integer part of a fixed-point number when the integer part is zero, regardless of zeros in the format model. |
|
C |
C999 |
Displays the ISO currency symbol in the specified position. |
|
D |
99D99 |
Displays the decimal character to separate the integral and fractional parts of a number. |
|
EEEE |
9.999EEEE |
Displays value in scientific notation (format must contain exactly four "E"s). |
|
G |
9G999 |
Displays the group separator in the specified positions in the integral part of a number. |
|
L |
L999 |
Displays the local currency symbol in the specified position. |
|
MI |
9999MI |
Displays a trailing minus sign after a negative value. Display a trailing space after a positive value. |
|
PR |
9999PR |
Displays a negative value in <angle brackets>. Displays a positive value with a leading and trailing space. |
|
RN rn |
RN rn |
Displays uppercase Roman numerals. Displays lowercase Roman numerals. Value can be an integer between 1 and 3999. |
|
S |
S9999 9999S |
Displays a leading minus or plus sign. Displays a trailing minus or plus sign. |
|
TM |
TM |
Displays the smallest number of decimal characters
possible. The default is |
|
U |
U9999 |
Displays the dual currency symbol in the specified position. |
|
V |
999V99 |
Displays value multiplied by 10n, where n is the number of 9's after the V. |
|
X |
XXXX xxxx |
Displays the hexadecimal value for the rounded value of the specified number of digits. |
The MI and PR format elements
can only appear in the last position of a number format model. The
S format element can only appear in the first or last
position.
If a number format model does not contain the
MI, S, or PR format elements,
negative return values automatically contain a leading negative sign, and positive
values automatically contain a leading space.
A number format model can contain only a single decimal character (D) or period (.), but it can contain multiple group separators (G) or commas (,). A group separator or comma cannot appear to the right of a decimal character or period in a number format model.
SQL*Plus formats NUMBER data right-justified. 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 COLUMN
FORMAT or SET NUMFORMAT, then the column's width will
always be at least the value of SET
NUMWIDTH.
SQL*Plus may round your NUMBER data to fit your format
or field width.
If a value cannot fit in the column, SQL*Plus displays pound signs (#) instead of the number.
If a positive value is extremely large and a numeric overflow occurs when rounding a number, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and a numeric overflow occurs when rounding a number, then the negative infinity sign replaces the value (-~).
HEA[DING] textDefines a
column heading. If you do not use a
HEADING clause, the column's heading defaults to column
or expr. If text contains blanks or punctuation characters, you must enclose
it with single or double quotes. Each occurrence of the HEADSEP character (by default,
"|") begins a new line.
For example,
COLUMN LAST_NAME HEADING 'Employee |Name'
would produce a two-line column heading.
See SET HEADS[EP] { |
c | ON | OFF} for information on changing the HEADSEP
character.
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}Aligns the heading. If you do not
use a JUSTIFY clause, headings for NUMBER columns
default to RIGHT and headings for other column types default to
LEFT.
LIKE {expr | alias} Copies the display attributes of another column
or expression (whose attributes you have already defined with another
COLUMN command). LIKE copies only attributes
not defined by another clause in the current COLUMN
command.
NEWL[INE]
Starts a new line before
displaying the column's value. NEWLINE has the same effect as
FOLD_BEFORE. NEWL[INE] does not work in SET MARKUP HTML
ON mode unless PREFORMAT is SET
ON.
NEW_V[ALUE] variableSpecifies a variable to hold a column value. You can reference the variable in
TTITLE commands. Use
NEW_VALUE to display column values or the date in the top title. You must include
the column in a BREAK command with the SKIP PAGE
action. The variable name cannot contain a pound sign (#).
NEW_VALUE is useful for primary/detail reports in which there is a new primary record
for each page. For primary/detail reporting, you must also include the column in the
ORDER BY clause. See the example at the end of this command
description.
Variables specified with NEW_V[ALUE]
are expanded before TTITLE is executed. The resulting string is
stored as the TTITLE text. During subsequent execution for each
page of the report, the expanded value of a variable may itself be interpreted as a
variable with unexpected results.
You can avoid this double
substitution in a TTITLE command by not using the
& prefix for NEW_V[ALUE] variables that
are to be substituted on each page of the report. If you want to use a substitution
variable to insert unchanging text in a TTITLE, enclose it in
quotes so that it is only substituted once.
For
information on displaying a column value in the bottom title, see
OLD_V[ALUE] variable below. For more information on referencing
variables in titles, see the TTITLE
command. For information on formatting and valid format models, see
FOR[MAT] format
above.
NOPRI[NT] | PRI[NT]
Controls the
printing of the column (the column heading and all the selected values).
NOPRINT turns off the screen output and
printing of the column.
PRINT turns the printing of the column
ON.
NUL[L] textControls the text SQL*Plus displays for null values in the given column. The
default is a white space. SET NULL controls the text displayed for
all null values for all columns, unless overridden for a specific column by the
NULL clause of the COLUMN command. When a
NULL value is selected, a variable's type always becomes
CHAR so the SET NULL text can be stored in
it.
OLD_V[ALUE] variableSpecifies a variable to hold a column
value. You can reference the variable
in
BTITLE commands. Use
OLD_VALUE to display column values in the bottom title. You
must include the column in a BREAK command with the SKIP
PAGE action.
OLD_VALUE is useful
for primary/detail reports in which there is a new
primary record for each page. For primary/detail reporting, you must also include
the column in the ORDER BY clause.
Variables
specified with OLD_V[ALUE] are expanded before
BTITLE is executed. The resulting string is stored as the
BTITLE text. During subsequent execution for each page of the
report, the expanded value of a variable may itself be interpreted as a variable
with unexpected results.
You can avoid this double substitution
in a BTITLE command by not using the & prefix
for OLD_V[ALUE] variables that are to be substituted on each page
of the report. If you want to use a substitution variable to insert unchanging text
in a BTITLE, enclose it in quotes so that it is only substituted
once.
For information on displaying a column
value in the top title, see the NEW_V[ALUE] variable. For more
information on referencing variables in titles, see the TTITLE command.
ON | OFF
Controls the status of display attributes for a
column. OFF
disables the attributes for a column without
affecting the attributes' definition. ON reinstates the attributes.
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
Specifies how SQL*Plus will treat a data type or
DATE string that is too wide for a column.
WRAPPED wraps the string within the column bounds, beginning
new lines when required. When WORD_WRAP is enabled, SQL*Plus left
justifies each new line, skipping all leading whitespace (for example, returns,
newline characters, tabs and spaces), including embedded newline characters.
Embedded whitespace not on a line boundary is not skipped.
TRUNCATED truncates the string at the end of the first line of
display.
NCLOB, BLOB,
BFILE, or multibyte CLOB columns cannot be
formatted with the WORD_WRAPPED option. If you format an
NCLOB, BLOB, BFILE, or
multibyte CLOB column with COLUMN WORD_WRAPPED,
the column data behaves as though COLUMN WRAPPED was applied
instead.
BOOLEAN Columns
Starting with
Release 26ai, the COLUMN command for BOOLEAN data
type sets the output format for the values returned from the database to a different
text literal other than TRUE or FALSE.
Syntax
COLUMN column BOOLEAN TEXT1 [TEXT2] where,
TEXT1 is the text to
represent the BOOLEAN value TRUE returned from the
database. It is a mandatory value.
TEXT2 is an
optional value to represent the BOOLEAN value
FALSE returned from the database. When TEXT2
is not provided, the value returned from the database is displayed.
For more information about BOOLEAN values, see the
Oracle
Database SQL Language Reference.
Usage
The COLUMN commands you enter can control a column's display attributes for multiple SQL SELECT commands.
You can enter any number of COLUMN commands for one or more columns. All column attributes set for each column remain in effect for the remainder of the session, until you turn the column OFF, or until you use the CLEAR COLUMN command.
When you enter multiple COLUMN commands for the same column, SQL*Plus applies their clauses collectively. If several COLUMN commands apply the same clause to the same column, the last one entered will control the output.
Examples
You can make the LAST_NAME column 20 characters wide and display EMPLOYEE NAME on two lines as the column heading:
COLUMN LAST_NAME FORMAT A20 HEADING 'EMPLOYEE|NAME'
You can format the SALARY column so that it shows millions of dollars, rounds to cents, uses commas to separate thousands, and displays $0.00 when a value is zero:
COLUMN SALARY FORMAT $9,999,990.99
You can assign the alias NET to a column containing a long expression, display the result in dollar format, and display <NULL> for null values:
COLUMN SALARY+COMMISSION_PCT+BONUS-EXPENSES-INS-TAX ALIAS NET COLUMN NET FORMAT $9,999,999.99 NULL '<NULL>'
Note that the example divides this column specification into two commands. The first defines the alias NET, and the second uses NET to define the format.
Also, note that in the first command, you must enter the expression exactly as you entered it in the SELECT command. Otherwise, SQL*Plus cannot match the COLUMN command to the appropriate column.
You can wrap long values in a column named REMARKS as follows:
COLUMN REMARKS FORMAT A20 WRAP
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- --------------------
123 25-AUG-2001 144 This order must be s
hipped by air freigh
t to ORDIf you replace WRAP with WORD_WRAP, the REMARKS column looks like this:
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- ---------------------
123 25-AUG-2001 144 This order must be
shipped by air freight
to ORDIf you specify TRUNCATE, the REMARKS column looks like this:
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- --------------------
123 25-AUG-2001 144 This order must be sTo print the current date and the name of each job in the top title, enter the following. Use the EMPLOYEES table of the HR schema instead of EMP_DETAILS_VIEW.
For details on creating a date variable, see About Displaying the Current Date in Titles.
Your two-page report would look similar to the following report, with "Job Report" centered within your current linesize:
COLUMN JOB_ID NOPRINT NEW_VALUE JOBVAR
COLUMN TODAY NOPRINT NEW_VALUE DATEVAR
BREAK ON JOB_ID SKIP PAGE ON TODAY
TTITLE CENTER 'Job Report' RIGHT DATEVAR SKIP 2 -
LEFT 'Job: ' JOBVAR SKIP 2
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') TODAY,
LAST_NAME, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, DEPARTMENT_ID
FROM EMPLOYEES WHERE JOB_ID IN ('MK_MAN', 'SA_MAN')
ORDER BY JOB_ID, LAST_NAME;You can change the default format of DATE columns to YYYY-MM-DD:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
Session altered.Execute the SELECT statement to display the change:
SELECT HIRE_DATEFROM EMPLOYEESWHERE EMPLOYEE_ID = 206;
The following output is displayed:
Job Report 04/19/01
Job: SA_MAN
HIRE_DATE
----------
1994-06-07See ALTER SESSION for information on the ALTER SESSION command.
You can change the default BOOLEAN value for COL1 from TRUE and FALSE to YES and NO:
SET NULL BLANK
COLUMN COL1 BOOLEAN YES NOSELECT * FROM my_table; The following output is displayed:
ID COL1 COL2
1 YES BLANK
2 NO TRUE
3 YES TRUE
4 NO TRUE
5 NO TRUE
6 NO TRUE
7 YES FALSE
8 YES BLANK