Re: NUMBER format question
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: jfpark137@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 Dec 2007 01:26:39 -0800 (PST)
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@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
