Re: NUMBER format question

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