Controlling the Display of Text Columns in SQL*Plus

Lesson 3 introduced the COLUMN command broadly, covering headings, formats, wrapping, and the new BOOLEAN clause. This lesson narrows in on one specific, everyday task: controlling how text columns actually display, since text columns are the ones most likely to overflow a report's width and need real formatting decisions made about them. You will achieve the visual results you want mainly by controlling column width and wrap, but there is more to it than just those two settings, and this lesson covers the rest as well.

For a text column, the FORMAT clause controls the display width. The format specification string always starts with the letter A and is followed by a number indicating the display width for the column. For example, to limit a column named lesson_text to 20 characters wide on a report, use the following COLUMN command:

COLUMN lesson_text FORMAT A20

Any number could be used in place of 20, depending on how wide you want the column to be. Given a width of 20 characters, the following example shows how the column would display:

LESSON_TEXT
--------------------
For a text column, the FORMAT clause
controls the
display width.

If you do not like the way SQL*Plus wraps a long value in a text column, you can specify a different behavior. The WRAPPED, WORD_WRAPPED, and TRUNCATED keywords control how text displays when it extends beyond the right edge of the column. The default behavior, with none of these keywords specified, is to wrap columns when the text hits the right edge, and the result is that lines are often wrapped right in the middle of a word. The lesson_text column above is a good example of this. You can prevent words from being broken in the middle by adding the WORD_WRAPPED keyword to the COLUMN command:

COLUMN lesson_text FORMAT A20 WORD_WRAPPED

The result is often much more readable. The lesson_text column will now display like this:

LESSON_TEXT
--------------------
For a text column,
the FORMAT clause
controls the display
width.

If you want a text field to display on one line only, no matter how long the value is, use the TRUNCATED keyword in place of WORD_WRAPPED:

COLUMN lesson_text FORMAT A20 TRUNCATED

With TRUNCATED in place, any long value is simply chopped to match the column width; nothing wraps to a second line at all.

The WRAPPED, WORD_WRAPPED, and TRUNCATED keywords set behavior for one column at a time, but SQL*Plus also has a session-wide default that applies when you have not specified anything at all for a given column. That default is controlled by SET WRAP:

SET WRAP ON
SET WRAP OFF

With WRAP set to ON, the default, a row too long for the current line width wraps to the next line. With WRAP set to OFF, that same row gets truncated instead. This is the session-level switch that the plain wrapping behavior shown earlier is actually coming from.

The relationship between SET WRAP and the COLUMN-level keywords is worth understanding clearly, since it is easy to assume one always overrides the other: SET WRAP establishes the default for every column in the session, and the WRAPPED and TRUNCATED clauses of the COLUMN command let you override that default for one specific column without changing it everywhere else. In practice, this means you can leave SET WRAP ON as a sensible session-wide default, then apply TRUNCATED to just the one or two columns in a report where you specifically want truncation instead, rather than switching the whole session's behavior back and forth.

Wide text columns often benefit from a heading that spans more than one line, so the heading itself does not force the column wider than the data needs to be. To split a heading onto multiple lines, use a vertical bar where you want a new line to begin:

COLUMN lesson_text HEADING 'Lesson|Text' FORMAT A20

This displays "Lesson" on the first line of the heading and "Text" directly beneath it, letting a two-word heading fit comfortably over a column that is only wide enough for the data itself. If the vertical bar is not the character you want to use for this, for instance because it might legitimately appear in your data, you can change it by setting the HEADSEP variable to a different character instead.

A text column that contains database NULLs will, by default, display as blank space, which can look like an error rather than a deliberate absence of data. The NULL clause of the COLUMN command lets you specify text to display instead:

COLUMN comments FORMAT A25 NULL '(none)'

With this in place, any row where comments is NULL displays the literal text (none) rather than an ambiguous blank space, making it clear to anyone reading the report that the absence of data was checked and confirmed, not simply overlooked.

Sometimes you need a column in your SELECT statement, perhaps to sort by it or reference it in another calculation, without actually wanting it to appear in the report output. The NOPRINT clause handles exactly this:

COLUMN internal_notes NOPRINT

The column remains part of the query and available to SQL*Plus internally, but it simply does not print as part of the visible report.

Once you have applied FORMAT, WRAPPED, HEADING, or NULL settings to a column, those settings persist for the rest of the SQL*Plus session, exactly as covered in Lesson 3. If you want to remove them and return to SQL*Plus's default display for a specific column, use CLEAR:

COLUMN lesson_text CLEAR

To reset every column in the session at once rather than one at a time, use CLEAR COLUMNS instead.

The FORMAT An technique covered throughout this lesson is not limited to CHAR and VARCHAR2 columns. It also applies to DATE columns when you want to control their display width as a fixed character count:

COLUMN hire_date FORMAT A11

Large object types, LONG, BLOB, BFILE, CLOB, NCLOB, and XMLType, work a little differently. Their display width is influenced first by two SET commands, SET LONG and SET LONGCHUNKSIZE, which control the maximum width used for displaying these values and the size of the increments SQL*Plus retrieves them in. You can still layer a COLUMN ... FORMAT An clause on top of these settings to further constrain how much of a large object's content actually appears in your report, but SET LONG in particular is worth checking first if a CLOB or XMLType column is displaying far more, or far less, text than you expected.

None of the formatting covered in this lesson changes anything about the underlying table. Every FORMAT, WRAPPED, HEADING, NULL, and NOPRINT setting applies only to the current SQL*Plus session or script; the data itself, and the table's actual column definitions, remain exactly as they were before you typed a single COLUMN command.