Re: Strange result in SQL*Plus

vafanassiev_at_aapt.com.au (Vsevolod Afanassiev) writes:

In SQL*Plus, I'd recommend always using an explicit COLUMN command to set a desired field width. This is specially true when the column is a SQL function.

Some NLS environment settings require the RDBMS and/or SQL*Plus to allocate extra storage in case multibyte characters are used. This commonly manifests itself as a double-width field when NLS_LANG is set differently on a particular client machine.

Setting cursor_sharing = force in the init.ora can cause SQL-function columns to double in width. (If you have Metalink access, see bug 2092801).

Also, the buffer sizing for SQL functions has been known to change from version to version of the RDBMS.

There are no width ambiguities using COLUMN.

Chris

> Let's say you have a VARCHAR2(48) column and you
> truncate it to 10 characters using substr.
> Then SQL*Plus automatically adjusts the
> default column width
>
> SELECT substr(program,1,10) from v$session;
>
> SUBSTR(PRO
> ----------
>
>
>
> JDBC Thin
> sqlplus_at_gl
>
> I don't know how it does it. However, on one of our boxes
> this behaviour suddenly changed (that's wat Apps Support is telling me)
> and SQL*Plus no longer adjusts the column width
>
> SELECT substr(program,1,10) from v$session;
>
> SUBSTR(PROGRAM,1,10)
> --------------------------------------------
>
>
>
> JDBC Thin Client
>
>
> Normally this won't be a big problem but our developers
> produced some number of reports that expect the default behaviour
> (i.e. truncation). I checked SQL*Plus manuals but couldn't find anything.
>
> Thanks
>
> Sev

-- 
Christopher Jones, Oracle Corporation, Australia.