Re: NUMBER format question

Thank you to everyone who has replied. I have more information.

Both DB's are 9.2.0.4 running on SunOS 5.8.

Both of the DB's login.sql and glogin.sql are identical.

There is no formatting being set on the column.

A query of the NLS settings for Session, Instance and Database return identical results.

On Dec 6, 2007 4:26 AM, Nigel Thomas <nigel_cl_thomas_at_yahoo.com> wrote:

> James, as noone else has replied, here's my 2d worth (yes, I am that old).
>
>
> SQL*Plus SHOW ALL shows you your SQL*Plus client settings. However you
> connect to DB1, you see decimals; however you connect to your DB2, you see
> no decimals unless you explicitly TO_CHAR the column with an appropriate
> numeric format, or explicitly SET NUMFORMAT to an non-default value (eg "9,
> 999.99"),
>
> Another possibility is that one (or both) columns use a SQL*Plus COLUMN
> format (and the two are different - either different column names/aliases.
> or the act of connection to DB2 causes a difference).
>
> You can check the column format by typing
> SQL> COLUMN mycolumn
> and SQL*Plus reports the format - eg:
> COLUMN MYCOLUMN ON
> HEADING 'My Heading'
> FORMAT 9999
>
> If the settings are different, you may be running a glogin.sql/login.sql -
> comb through that to see what's going on. A clever dick can easily make
> COLUMN settings conditional on which user or database you are connected to.
> And recall that on databases prior to 10g, glogin/login is only executed on
> first connection with SQL*Plus.
>
>
> If there is still no obvious difference, we could infer it is a
> database-side question.
>
> How can you affect the display of a numeric column when it is implicitly
> cast?
>
> - we can modify the nls_language or nls_territory of the database (you can
> use ALTER SESSION to demonstrate)
> - we can modify the nls_numeric_characters (which are usually indirectly
> set by territory)
>
> Are there any nls_territories which don't display decimals by default? Not
> that I can easily find. However, it's worth comparing the
> NLS_SESSION_PARAMETERS on DB1 and DB2.
>
> SELECT * from NLS_SESSION_PARAMETERS; (or INSTANCE or DATABASE)
>
> Good luck
>
> Nigel
>
>
>
> ----- Original Message ----
> From: James Parkhurst <jfpark137_at_gmail.com>
> To: oracle-l_at_freelists.org
> Sent: Wednesday, December 5, 2007 6:39:48 PM
> Subject: NUMBER format question
>
> I have two tables on two different DB's, within two different servers.
> Both tables have the same structure and both DB's are of the same version (
> 9.2.0.4). The column in question is of datatype NUMBER, w/o scale or
> precision.
>
> If I log on to Server1, connect to DB1, and query the table's column, the
> results are returned in SQL*Plus with the decimal portion of the value
> intact.
>
> If I log onto Server2, connect to DB2, and query the table's column, the
> results are returned in SQL*Plus w/o the decimal portion of the value.
>
> If I log onto Server1, start SQL*Plus, then connect to DB2 remotely and
> query the data, the results are returned in SQL*Plus w/o the decimal portion
> of the value.
>
> The session settings are identical via the SHOW ALL, and I've verified
> that the decimals are present on DB2 using a to_char and setting NUMFORMAT.
>
> Anyone have any ideas why?
>
> James
>
>

--
http://www.freelists.org/webpage/oracle-l