Output widths change after upgrade, or change of character set (Doc ID 330717.1)

Last updated on AUGUST 28, 2023

Applies to:

Purpose

You might find that the output in SQL*Plus of certain columns is not as expected.
For example you have created a column as VARCHAR2(10), but when you select the column in sqlplus the display width is larger than that (the heading might take 20, 30 or 40 places for example).
There are 2 common situations that may exhibit this behavior - both look quite different but have the same underlying reason.

Scope

1. One obvious cause is:

There's always a chance that login.sql or glogin.sql or some other script that gets invoked below the covers is different between two environments and those scripts issue different "column xxx format yyy" type sqlplus commands. The "column" command by itself or followed by a column_name argument should list any existing formatting directives that may have been set in SQL*Plus.

2. It is common for this to be noticed after a change of database character set from a single-byte character set to a multi-byte character set. Another common reason when the behavior changes is when the 9203 patch set is applied to the database or you've changed the setting of the init.ora parameter CURSOR_SHARING.

Examples of when you might run into these symptoms are:

Changed database character set to UTF8 or AL32UTF8
Upgraded database to 9.2.0.3 or higher, and you use CURSOR_SHARING set to SIMILAR or FORCE.
You already had a database version of 9203 or higher but you've changed the setting of CURSOR_SHARING from EXACT to either SIMILAR or FORCE.
After any of these changes you might find that the results of a query are not formatted as you were expecting in SQL*plus.

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.