This discussion is archived
6 Replies Latest reply: Jul 24, 2012 2:07 PM by 951503 RSS

11gR2 sqlplus results have extra-wide column widths?

915010 Newbie
Currently Being Moderated
We're upgrading from 11gR1 to 11gR2 and have hit upon an anomoly with sqlplus.

The following query yields different results when run in R1 (and any version prior) versus R2:

SELECT     rpad('Hello World', 5)     Hdr
FROM     DUAL;

R1 results:

HDR
=====
Hello


R2 results:

HDR
===============
Hello


Notice that the R2 result is actually 15 characters long, as evidenced by the header underlines (changed to '=' for this post).     This may seem trivial, but we have hundreds of scripts that look something like the following:

SELECT     rpad(A,     20)     First,
     rpad(B,     30)     Second,
     rpad(C,     10)     Third,
     rpad(D,     15)     Fourth
FROM     FOO
WHERE     ...


I've checked the character sets between the R1 and R2 instances and they're the same:

NLS_CHARACTERSET     WE8MSWIN1252
NLS_NCHAR_CHARACTERSET     AL16UTF16

When connecting sqlplus R2 to the R1 instance, I get the new (undesired) behavior. Unfortunately, I cannot connect sqlplus R1 to an R2 instance at this time.

I'm running R1 on Fedora 7 and R2 on Fedora 15; LANG=en_US.UTF-8 on both.

Questions:

1) Why is sqlplus behaving differently in R2?

2) What can we do to obtain the old (R1 and earlier) behavior?

Any help is appreciated.
  • 1. Re: 11gR2 sqlplus results have extra-wide column widths?
    19426 Guru
    Currently Being Moderated
    I cannot reproduce this:

    SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 3 10:34:38 2012

    Copyright (c) 1982, 2011, Oracle. All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
    With the Partitioning option

    SQL> select rpad('Hello World', 5) Hdr from dual;

    HDR
    =====
    Hello

    If you don't have the latest patchlevel installed , it may be a bug in an earlier version.


    Werner

    Edited by: oradba on 03.02.2012 10:44
  • 2. Re: 11gR2 sqlplus results have extra-wide column widths?
    riedelme Expert
    Currently Being Moderated
    1) Why is sqlplus behaving differently in R2?
    Dunno. Is there something odd in a glogin.sql or login.sql initialization file?

    Is it worth the effort to install a R1 client on a PC to check R1behavior?
  • 3. Re: 11gR2 sqlplus results have extra-wide column widths?
    915010 Newbie
    Currently Being Moderated
    OK, I tried without using any login.sql file: no joy. Next, I managed to establish a connection from an 11.1 sqlplus client to my 11.2 instance: it did not exhibit the anomaly.

    Since we are running 11.2.0.1.0, I will try patching to the latest release and see if that fixes things.
  • 4. Re: 11gR2 sqlplus results have extra-wide column widths?
    915010 Newbie
    Currently Being Moderated
    I installed 11.2.0.3.0 and the problem still exists.

    I'm not sure what to try next. Maybe I can write a simple OCI program that prints out what Oracle thinks the column widths coming back from the server are. If the widths are good, that would indicate a bug in sqlplus.

    Any other suggestions? A colleague says we should install an 11gR1 client on our development server and use that for running sqlplus, but that's not how we'll be deploying and we'd like to be able to use our existing code as-is in production.
  • 5. Re: 11gR2 sqlplus results have extra-wide column widths?
    user7382048 Newbie
    Currently Being Moderated
    we are experiencing exactly the same problem (in our case 100s of sqlplus scripts utilising substr) - and this is the first mention I've found of the problem, so will be very interested in any solution

    Edited by: user7382048 on 13-Feb-2012 07:49
  • 6. Re: 11gR2 sqlplus results have extra-wide column widths?
    951503 Newbie
    Currently Being Moderated
    I also just ran into this problem, and it is quite annoying.

    This might help with the problem: Note 330717.1 Output widths change after upgrade.

    It seems that you might have upgraded to the AL32UTF8 character set from an 8-bit character set. Check your NLS_CHARACTERSET setting:
    select * from SYS.NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';

    But this doesn't answer the burning question - Is there a way to force column widths to be handled the way they used to be (without needing to explicitly define a format for each column)?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points