Home » SQL & PL/SQL » Client Tools » Displaying Numbers greater than 15 digits (Oracle 9i)
Displaying Numbers greater than 15 digits [message #383425] Wed, 28 January 2009 17:39 Go to next message
prax_14
Messages: 64
Registered: July 2008
Member
Hi
I have a Table metrics. One of the column mt_type should hold huge numeric and floating values. So I made the date type of that column as NUMBER(22,2). I Inserted a 20 digit number in mt_type column of metrics table as shown below. When i am trying to run a select statement and display the values I am not able to display all the 20 digits. I even tried changing the following values but i am not able to display all the 20 digits as it is.

SET NUMFORMAT 99999999999999999999
SET NUMWIDTH 25
column MT_TYPE FORMAT 99999999999999999999

The last five digits is getting replaced by zeros.



CREATE TABLE METRICS
( MT_NAME                  varchar2(30),
  MT_TYPE                  NUMBER(22,2)
);

Insert into metrics values('test',11111111111111111111);

SET NUMFORMAT 99999999999999999999
SET NUMWIDTH 25
column mt_value FORMAT 99999999999999999999

select MT_TYPE from metrics;

             MT_TYPE
---------------------
 11111111111111100000

1 row selected.





as you can see above last five digits is replace by zeros while displaying.


Can any one help with the correct setting that needs to be done to display all the 20 digits as it is.


Re: Displaying Numbers greater than 15 digits [message #383450 is a reply to message #383425] Wed, 28 January 2009 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE TABLE METRICS
  2  ( MT_NAME                  varchar2(30),
  3    MT_TYPE                  NUMBER(22,2)
  4  );

Table created.

SQL> SET NUMFORMAT 99999999999999999999
SQL> SET NUMWIDTH 25
SQL> column mt_value FORMAT 99999999999999999999
SQL> 
SQL> select MT_TYPE from metrics;

              MT_TYPE
---------------------
 11111111111111111111

1 row selected.

Done on database 9.2.0.8 with client 10.2.0.4.
What is your server AND client versions with 4 decimals.

Regards
Michel

[Updated on: Wed, 28 January 2009 23:34]

Report message to a moderator

Re: Displaying Numbers greater than 15 digits [message #383666 is a reply to message #383450] Thu, 29 January 2009 10:37 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Server : 9.2.0.7.0
Client : 11.1.0.6.0

I also tried with client 9.2.0.1.0, and got the same result.

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 29 11:20:02 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> SET NUMFORMAT 99999999999999999999
SQL> SET NUMWIDTH 25
SQL> column mt_value FORMAT 99999999999999999999
SQL> select mt_value from dprt_metrics where mt_id = 43;

             MT_VALUE
---------------------
 11111111111111100000

Re: Displaying Numbers greater than 15 digits [message #383672 is a reply to message #383666] Thu, 29 January 2009 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you did all the steps you mentionned exactly as it with SQL*Plus?
Please copy and paste the whole session as I did including create table and insert statements.

Regards
Michel
Re: Displaying Numbers greater than 15 digits [message #383750 is a reply to message #383425] Thu, 29 January 2009 23:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


SQL> column mt_value FORMAT 99999999999999999999
SQL> 
SQL> select MT_TYPE from metrics;

              MT_TYPE
---------------------
 11111111111111111111

1 row selected.


Formatting column is mt_value and selection column is MT_TYPE .


Smile
Rajuvan.
Re: Displaying Numbers greater than 15 digits [message #383751 is a reply to message #383750] Thu, 29 January 2009 23:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good catch! Wink

Regards
Michel
Re: Displaying Numbers greater than 15 digits [message #383759 is a reply to message #383751] Fri, 30 January 2009 00:27 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/449/0/
Previous Topic: oracle 9i works with windows vista
Next Topic: How can i use iSQL * plus in my local system
Goto Forum:
  


Current Time: Sat Dec 10 20:17:33 CST 2016

Total time taken to generate the page: 0.06317 seconds