I have a number of sql reports that used to work fine. Recently however they have started to be outputted with the column widths being longer than they should. According to Oracle’s documentation if a column in an sql report has no explicit “COLUMN” definition at the start of the report, eg ‘column example_col heading "Eg col" format A50‘, it should display with a width that is the same as the column’s definition in the database table, ie if the column is defined as varchar2(10) it should display with a width of 10 characters.
Previously this was fine however recently some of these columns have started being displayed with a larger width which is causing the lines of the report to extend past the linesize onto a second line thus making the report less readable.
I just wanted to see if anybody may have come across this behaviour before and might know what could be the cause? Below are before and after pictures of one of the reports so you can see the problem.
report before problems started
Advertisement
Answer
It turns out the problem is in setting the NLS_LANG environment variable to AMERICAN_AMERICA.AL32UTF8. This causes column widths to be extended when output in SQLPlus. The solution was to change this environment variable to AMERICAN_AMERICA.WE8ISO8859P1.
The details from the relevant Oracle Metalink are as follows:
Symptoms Running the same statement in different SQL*Plus sessions results in different length off the output.
Session 1:
SQL> select sysdate, sysdate from dual; SYSDATE SYSDATE
30-OCT-08 30-OCT-08
Session 2:
SQL> select sysdate, sysdate from dual; SYSDATE SYSDATE
30-OCT-08 30-OCT-08
Cause The NLS_LANG character set determines how the output will be presented.
When using multibyte character sets column widths will be larger. Solution Set character set for NLS_LANG to get wanted output. If you need “long” output, use UTF8 as character set. Make sure the client OS can handle the character set chosen.