The COLUMN command is used to format report output for columnar reports. Using this command, you can control column width, the column title, the way numbers are displayed, whether or not long values wrap to a new line, and a host of other things. Chapter 3 discusses this command.
COL[UMN] [column_name[ALI[AS]alias| CLE[AR]| FOLD_A[FTER]| FOLD_B[EFORE]| FOR[MAT]format_spec| HEA[DING]heading_text| JUS[TIFY] {LEFT|CENTER|CENTRE|RIGHT}| LIKEsource_column_name| NEWL[INE]| NEW_V[ALUE]user_variable| NOPRI[NT]| PRI[NT]| NUL[L]null_text| OLD_V[ALUE]user_variable| ON| OFF| TRU[NCATED]| WOR[D_WRAPPED]| WRA[PPED]...]]
where:
- COL[UMN]
May be abbreviated to COL. Issuing the COLUMN command with no parameters gets you a list of all current column formats.
- column_name
Is the name of the column you are formatting. If it is a computed column, the expression is the name. If your SELECT statement aliases the column, you must use that alias name here. Issuing the command COLUMN column_name with no further parameters causes SQL*Plus to display the current format for that column.
- ALI[AS] alias
May be abbreviated ALI. ALIAS allows you to specify an alternate name for this column that is meaningful to SQL*Plus. Do not confuse this with the column alias in a SELECT statement.
- alias
Is an alternate name for the column that may be used in BREAK commands, COMPUTE commands, and other COLUMN commands.
- CLE[AR]
May be abbreviated to CLE. CLEAR erases any format settings for the column in question. This puts you back to the way things were before any COLUMN commands were issued for the column.
- FOLD_A[FTER]
May be abbreviated to FOLD_A, and causes SQL*Plus to advance to a new line before displaying the next column. In other words, the output is wrapped after this column prints.
- FOLD_B[EFORE]
May be abbreviated to FOLD_B. This is the opposite of FOLD_AFTER, and causes SQL*Plus to wrap to a new line before this column is printed.
- FOR[MAT]
May be abbreviated to FOR, and allows you to control how the data for the column is displayed. For text fields, you can control the width. For numeric fields, you can control the width, placement of commas, placement of the dollar sign, and so on.
- format_spec
Is a string that specifies the display format for the column. Appendix B describes the format specification elements that may be used with the COLUMN command.
- HEA[DING]
May be abbreviated HEA, and allows you to define a heading for the column. The heading text displays at the top of each column, and is redisplayed every time a page break occurs.
- heading_text
Is the text you want for the column heading. You should enclose this in quotes, but you don’t have to if the heading is a single word. Either single or double quotes may be used. If you need to include a quote as part of your heading, use two quote characters back to back.
- JUS[TIFY]
May be abbreviated JUS, and controls where the heading text prints relative to the column width. By default, headings for numeric fields print flush right, and headings for text fields print flush left. This parameter allows you to change that behavior. You must follow this keyword with one of the following: LEFT, RIGHT, CENTER, or CENTRE. LEFT causes the heading to print flush left. RIGHT causes the heading to print flush right. CENTER and CENTRE cause the heading to print centered over the top of the column. Note that this parameter has no effect whatsoever on how the data for the column is displayed.
- LIKE
Causes the column to be defined with the same format attributes as another column. LIKE must be followed by a column name, and that column becomes the source column.
- source_column_name
Is the name of the source column used with the LIKE parameter.
- NEWL[INE]
May be abbreviated NEWL. This is the same as FOLD_BEFORE. It causes SQL*Plus to wrap to a new line before the column is printed.
- NEW_V[ALUE]
May be abbreviated NEW_V, and causes SQL*Plus to keep a user variable updated with the current value of the column. The user variable is updated whenever the column value changes.
- user_variable
Is the name of a user variable for use with the NEW_VALUE and OLD_VALUE parameters.
- NOPRI[NT]
May be abbreviated NOPRI, and tells SQL*Plus not to print the column. NOPRINT is sometimes used when you just want to get a column value into a user variable (see NEW_VALUE), but you don’t want it displayed. This is often done when generating master/detail reports.
- PRI[NT]
May be abbreviated to PRI, and is the opposite of NOPRINT. Use PRINT when you want to turn printing back on for a column.
- NUL[L]
May be abbreviated NUL, and allows you to specify text to be displayed when the column value is null.
- null_text
Is the text you want displayed when the column in question is null. As with the heading text, this may optionally be enclosed in quotes.
- OLD_V[ALUE]
This may be abbreviated to OLD_V, and must be followed by a user variable name. OLD_VALUE works like NEW_VALUE, except that when the column changes, the previous value is stored in a user variable. This is useful when you need to print a value in the page footer of a master/detail report.
- ON
Causes SQL*Plus to print the column using the format you have specified. This is the default behavior. You don’t need to use ON unless you have previously used OFF.
- OFF
Disables the format settings for the column. SQL*Plus acts as if you had never issued any COLUMN commands for the column in question.
- TRU[NCATED]
May be abbreviated TRU, and causes the column text to be truncated to the width of the column. Longer values are not wrapped.
- WOR[D_WRAPPED]
May be abbreviated WOR. WORD_WRAPPED is similar to WRAPPED, but line breaks occur at word boundaries. Words that are longer than the column is wide will still be broken at the column boundary.
- WRA[PPED]
May be abbreviated WRA. WRAPPED affects the printing of values that are longer than the column is wide, and causes SQL*Plus to wrap those values to a new line as many times as necessary in order to print the entire value. Line breaks will occur exactly at the column boundary, even in the middle of a word.