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
NUMBER
data -
Wrapping of column data
-
Change the default
BOOLEAN
value
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] alias
Assigns 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 ATTRIBUTE
s 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] format
Specifies 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] text
Defines 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] variable
Specifies 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] text
Controls 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] variable
Specifies 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 23ai, 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 ORD
If 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 ORD
If you specify TRUNCATE
, the REMARKS
column looks like this:
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- --------------------
123 25-AUG-2001 144 This order must be s
To 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-07
See 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 NO
SELECT * 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