| Lesson 3 | Column formatting |
| Objective | Use SQL*Plus COLUMN command to format output. |
SQL*Plus COLUMN command
The COLUMN command is used to format columns, and allows you to produce reports that are easy to read and understand.
The following diagram shows the syntax for the most commonly used elements of the COLUMN command.
- COLUMN: This is the command, and it may be abbreviated to COL.
- name: Represents the column name from the SQL query.
- HEADING: The HEADING keyword introduces the text that you want to use for the column heading. The default is to use the column name. HEADING may be abbreviated to HEA.
- text: Represents the heading text. Single quotes may be used instead of double. The quotes can be left off entirely if the heading is just one word, containing no special characters.
- FORMAT: Introduces the format specification that you want to use for the column. FORMAT may be abbreviated to FOR.
- spec: Represents the column's format specification. This is a string of characters that controls how the column's data are displayed on the report.
- TRUNCATED: Tells SQL*Plus to truncate column values in order to make them fit the display width.
The abbreviation TRU may be used. - WORD_WRAPPED: Tells SQL*Plus to display long values using multiple lines, and to word-wrap the lines. In other words, a line will not be broken in the middle of a word. The abbreviation WOR may be used for this.
- WRAPPED: Tells SQL*Plus to display long values using multiple lines, and to break each line exactly at the display width regardless of whether or not the line break occurs in the middle of a word. The abbreviation WRA may be used.
- JUSTIFY: The JUSTIFY clause controls the manner in which the column heading is aligned over the column. JUSTIFY may be abbreviated to JUS.
- justification: May be either RIGHT, LEFT, or CENTER. For text columns, the default is LEFT. For numeric columns it is RIGHT.
The most critical clause in the COLUMN command is the FORMAT clause, which controls how column data are formatted.
The next three lessons talk about this in terms of text, numeric, and date column types.
The following paragraph discusses more about the COLUMN command.
The following paragraph discusses more about the COLUMN command.
The COLUMN command in Oracle
Here are the correct matches.| WORD_WRAPPED | Causes text in a column to wrap at word boundaries |
| TRUNCATED | Truncates columns with text values longer that the column’s display width |
| JUSTIFY LEFT | Forces a heading to print flush with the left edge of the column |
| HEADING (HEA) | Used to define your own heading for a column |
| WRAPPED | Tells text in a column to wrap when the column width is reached |
| FORMAT (FOR) | Used to define a display format for a column |
COLUMN
COL[UMN] [{column | expr} [option ...]]
where option represents one of the following clauses:
ALI[AS] alias
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
- alignment of the column heading
- format for NUMBER data
- wrapping of column data
Also lists the current display attributes for a single column or 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 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.
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.
