The SQL*Plus COLUMN Command in Oracle AI Database 26ai

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. The previous lesson used COLUMN briefly, three lines of it, to make a DBA_OBJECTS report readable. This lesson goes much deeper into what that command actually does, and everything it can do beyond simply setting a width.

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
• Control how native BOOLEAN columns display, using the BOOLEAN clause introduced in Oracle AI Database 26ai

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

COLUMN name [HEADING "text"]
       [FORMAT spec]
       [TRUNCATED | WORD_WRAPPED | WRAPPED]
       [JUSTIFY justification]
       [BOOLEAN text1 [text2]]

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 remains widely used for scripting and batch reporting, and everything shown here carries over directly to SQLcl as well.

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. It is worth previewing briefly here with a numeric example, since every example so far in this module has formatted text columns only:

SQL> COLUMN salary FORMAT $99,999.99

SQL> SELECT last_name, salary
  2  FROM   employees
  3  WHERE  department_id = 90;

LAST_NAME            SALARY
-------------------- -----------
King                 $24,000.00
Kochhar              $17,000.00
De Haan              $17,000.00

Here, FORMAT $99,999.99 is a numeric format model rather than a text-width specifier: it adds a dollar sign, inserts a comma at the thousands place, and displays exactly two decimal places. FORMAT models differ meaningfully depending on the underlying data type, which is exactly why the topic gets its own dedicated coverage later in this module rather than being fully explained here.

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, and FORMAT A15 and FORMAT A20 set fixed widths for the text columns. LOCKED(TIMED) here is a genuine, current account status you may actually see on a real database; it is not a placeholder value.

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. This trips people up more often than any other single detail about COLUMN: format a column once early in a long session, forget about it, and every later query against a column of that name inherits the same formatting whether you want it to or not.

The COLUMN command can also be used to list the current display attributes rather than set new ones:

• Enter COLUMN column_name (with no other clauses) to display the attributes currently defined for that column or expression.
• Enter COLUMN alone, with no arguments at all, to list attributes for every currently defined column in the session.

This is genuinely useful the moment a report's formatting stops making sense to you partway through a long session: rather than guessing what you set three queries ago, just ask SQL*Plus directly.

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).
BOOLEAN	Sets custom display text for a native BOOLEAN column's TRUE and FALSE values. New in Oracle AI Database 26ai.

COLUMN parameters (full syntax)

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

Here, option represents one of the following clauses:

ALI[AS] alias
BOOL[EAN] text1 [text2]
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, date, and now native BOOLEAN columns.
4. Choose how long column values are wrapped or truncated.

Oracle AI Database 26ai adds native support for a SQL BOOLEAN data type, and the COLUMN command was extended specifically to control how those values display. Without any formatting, a BOOLEAN column returns the literal values TRUE or FALSE from the database. The BOOLEAN clause lets you replace that with text of your own choosing:

COLUMN column BOOLEAN text1 [text2]

text1 is required, and represents the value displayed when the column is TRUE. text2 is optional and represents the value displayed when the column is FALSE; if you omit it, FALSE displays exactly as returned from the database.

A concrete example, changing a column's default TRUE and FALSE display to YES and NO instead:

SQL> COLUMN col1 BOOLEAN YES NO

SQL> SELECT * FROM my_table;

Any row where col1 is TRUE now displays YES; any row where col1 is FALSE now displays NO. This is a small feature, but a genuinely useful one the moment you are building a report meant for a non-technical audience who would find a column full of literal TRUE and FALSE values less immediately readable than YES and NO, or Active and Inactive, or any other pair of labels that fits the report's actual subject matter. For the full details on BOOLEAN as a SQL data type itself, rather than just how SQL*Plus displays it, see the Oracle Database SQL Language Reference.

The {column | expr} portion of the syntax identifies which result column you are formatting. This is typically a column name that appears in the SELECT list, but it can also be an expression or an alias. A few important rules apply:

• 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 share a name, assign a distinct alias in the SELECT command, then use COLUMN alias ... to format each one separately.
• 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. BREAK and COMPUTE are their own SQL*Plus commands, covered in a later lesson, but ALIAS is worth knowing about now since it is what lets those commands and COLUMN all refer consistently to the same column.

CLE[AR]
Resets the display attributes for the column to default values. To reset attributes for every column in the session at once, use the CLEAR COLUMNS command. CLEAR COLUMNS also clears any ATTRIBUTE settings for those columns, so it is a genuinely complete reset, not a partial one.

ENTMAP {ON | OFF}
Controls HTML entity mapping for selected columns in HTML output. This matters specifically 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, so they display correctly as literal text rather than being interpreted as markup.
• With ENTMAP OFF, SQL*Plus leaves these characters unchanged, which lets you embed valid HTML fragments, such as hyperlinks, directly 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 specific column. The default value for COLUMN ENTMAP follows the current setting of the MARKUP HTML ENTMAP option, so if you have not touched either setting, you are working with Oracle's own defaults for both.