Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 21467 invoked from network); 6 Dec 2007 09:45:15 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 6 Dec 2007 09:45:09 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B59FE7D45AF; Thu, 6 Dec 2007 10:44:34 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22704-08; Thu, 6 Dec 2007 10:44:34 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 211C67D451D; Thu, 6 Dec 2007 10:44:34 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 06 Dec 2007 09:57:37 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 621047D2954 for ; Thu, 6 Dec 2007 09:57:37 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11669-02 for ; Thu, 6 Dec 2007 09:57:37 -0500 (EST) Received: from hs-out-2122.google.com (hs-out-0708.google.com [64.233.178.247]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7A7C17D26E7 for ; Thu, 6 Dec 2007 09:57:36 -0500 (EST) Received: by hs-out-2122.google.com with SMTP id 23so92476hsn for ; Thu, 06 Dec 2007 06:57:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=CPxFHDoLWToPsLetlGzYZcvicMtAHTj7fOgXXiL1zHo=; b=piCPu2KK7iaBBC7eaJTcwFtJtq7SgbLGzDd1LcqtyBZk5zf+9KIVY88QoihzIFj4r+jdEOm6Yf6TUXkmeGnB5u+mt6LkNUoAHW7+3lgieVoeUTWsWnw+bmqS0YW6uZ+QjIZP9EtBQzcR72m2wjgYT9aDEwKnGPPLJdGB+UG7Wd4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=u6376S11x2UfPoEvjsRqaC2wfnhJlOMNId2O8/7GsvldLcuBhzj08UuJhrdOCeK/zeliX1c5L2GY0NPmuhqLdnOHiYt1L5Q8O4J9IAt2ds77v9P+aU02tzO2ApbN+lXzluki4qF57M46b1M3CozU9/lG4WRQHe36OEPR5v9gQB8= Received: by 10.70.35.1 with SMTP id i1mr3323352wxi.1196953053575; Thu, 06 Dec 2007 06:57:33 -0800 (PST) Received: by 10.70.67.8 with HTTP; Thu, 6 Dec 2007 06:57:33 -0800 (PST) Message-ID: <8e7ca0a00712060657w28f025e4mb796644b553ba717@mail.gmail.com> Date: Thu, 6 Dec 2007 09:57:33 -0500 From: "James Parkhurst" To: oracle-l@freelists.org Subject: Re: NUMBER format question In-Reply-To: <905916.49817.qm@web58803.mail.re1.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5615_1315741.1196953053547" References: <905916.49817.qm@web58803.mail.re1.yahoo.com> X-archive-position: 3750 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jfpark137@gmail.com Precedence: normal Reply-to: jfpark137@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_5615_1315741.1196953053547 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 > To: oracle-l@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 > > ------=_Part_5615_1315741.1196953053547 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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@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@gmail.com >
To: oracle-l@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


------=_Part_5615_1315741.1196953053547-- -- http://www.freelists.org/webpage/oracle-l