SQL*Plus COLUMN Command

The SQL*Plus COLUMN command controls how query results are displayed on the screen or in a report. You can change headings, widths, alignment, and wrapping behavior so that output is easier to read and suitable for printing or saving to a file.

In this lesson you will learn how to:

• Read the basic COLUMN syntax used in SQL*Plus
• Define readable column headings with HEADING
• Control display width with the FORMAT clause
• Choose between truncating and wrapping long values
• Align column headings using the JUSTIFY clause

The diagram below shows the most commonly used elements of the COLUMN command.

In modern environments you may use tools such as SQL*Plus, SQLcl, or other Oracle command-line clients. The examples in this lesson focus on traditional SQL*Plus behavior, which is still widely used for scripting and batch reporting.

The most critical part of the COLUMN command is often the FORMAT clause, which you will see again in the next lessons on text, numeric, and date formatting.

A simple query may produce column headings and widths that are technically correct but not very readable:

SQL> SELECT username, account_status
  2  FROM   dba_users;

USERNAME   ACCOUNT_STATUS
---------- -------------------------
SYS        OPEN
SYSTEM     OPEN
SCOTT      LOCKED(TIMED)
...

You can use COLUMN to improve the output:

SQL> COLUMN username      HEADING "User Name"   FORMAT A15
SQL> COLUMN account_status HEADING "Status"      FORMAT A20

SQL> SELECT username, account_status
  2  FROM   dba_users;

User Name       Status
--------------- --------------------
SYS             OPEN
SYSTEM          OPEN
SCOTT           LOCKED(TIMED)
...

In this example:

• HEADING replaces technical column names with friendly labels.
• FORMAT A15 and FORMAT A20 set fixed widths for the text columns.

Remember that COLUMN settings are session-level: once defined, they affect all subsequent queries in the same SQL*Plus session until you clear or override them.

The table below summarizes several important options you saw in the syntax diagram:

WORD_WRAPPED	Causes text in a column to wrap at word boundaries when the display width is exceeded.
TRUNCATED	Truncates column values that are longer than the column’s display width.
JUSTIFY LEFT	Forces a heading to print flush with the left edge of the column.
HEADING (HEA)	Defines your own heading text for a column instead of using the column name.
WRAPPED	Wraps text to multiple lines when the column width is reached, potentially breaking words in the middle.
FORMAT (FOR)	Defines the display format for a column (for example, text width, numeric format, or date mask).

COLUMN parameters (full syntax)

COL[UMN] [{column | expr} [option ...]]

Here, 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]

These options allow you to:

1. Set custom text for column headings.
2. Control alignment of headings.
3. Define formats for NUMBER, text, and date columns.
4. Choose how long column values are wrapped or truncated.

The COLUMN command can also be used to list the current display attributes:

• Enter COLUMN column_name (with no other clauses) to display the attributes for that column or expression.
• Enter COLUMN (with no arguments) to list attributes for all currently defined columns.

The {column | expr} portion of the syntax identifies which result column you are formatting:

{column | expr}

This is typically a column name that appears in the SELECT list, but it can also be an expression or an alias. There are a few important rules:

• If you use an expression in the SELECT statement, you must enter it in the COLUMN command exactly as it appears in the query or refer to it by its alias.
• If you select columns with the same name from different tables, a COLUMN command for that name applies to all such columns in the session. SQL*Plus ignores table name prefixes in SELECT statements.
• To format columns differently when they have the same name, assign a distinct alias in the SELECT command and then use COLUMN alias ... to format each one.
• Spaces in names are ignored unless the name is enclosed in double quotes. For quoted identifiers, always use the exact case and spacing.

• ALI[AS] alias
  Assigns a specified alias to a column for use in BREAK, COMPUTE, and other COLUMN commands. This does not change the name of the column in the underlying table; it is only for display and reporting within SQL*Plus.
• CLE[AR]
  Resets the display attributes for the column to default values. To reset attributes for all columns in the session, you can use the CLEAR COLUMNS command. CLEAR COLUMNS also clears any ATTRIBUTE settings for those columns.
• ENTMAP {ON | OFF}
  Controls HTML entity mapping for selected columns in HTML output. This is useful when you generate reports as HTML from SQL*Plus:
  • With ENTMAP ON (the default), special characters such as <, >, ", and & are replaced by their HTML entities.
  • With ENTMAP OFF, SQL*Plus leaves these characters unchanged, allowing you to embed valid HTML fragments such as hyperlinks in a column’s data.

  Entities in column headings and any COMPUTE labels or output appearing in the column are mapped or not mapped according to the ENTMAP setting for that column. The default value for COLUMN ENTMAP follows the current setting of the MARKUP HTML ENTMAP option.