RelationalDBDesign RelationalDBDesign


SQL* Plus CLI  «Prev  Next»
Lesson 4 Formatting text columns
ObjectiveControl the display of text columns.

Formatting Oracle Text Columns

When formatting text columns of a report you created using SQL*Plus, you can achieve the visual results you want by controlling column width and wrap.

Controlling column width

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 be displayed:
LESSON_TEXT

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

Controlling column wrap

If you do not like the way that SQL*Plus wraps a long value in a text column, you can specify a different behavior. The WRAPPED, WORD_WRAPPED, and TRUNCATED keywords may be used to control how text is displayed when it extends beyond the right edge of the column. The default behavior is to wrap columns when the text hits the right edge. 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, for example, 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, you can use the TRUNCATED keyword in place of WORD_WRAPPED. When you use TRUNCATE, any long values are chopped to match the column width.