Home » SQL & PL/SQL » SQL & PL/SQL » NUMBER column
NUMBER column [message #269908] Tue, 25 September 2007 02:03 Go to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi,

What is the maximum value that can be stored in a column defined as NUMBER datatype?

Thanks...
Re: NUMBER column [message #269909 is a reply to message #269908] Tue, 25 September 2007 02:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
This should get you started:
Datatype Limits
Datatypes

MHE
Re: NUMBER column [message #269943 is a reply to message #269908] Tue, 25 September 2007 03:50 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Thanks Maaher,

In terms of bytes,how many bytes will be allocated for a NUMBER column?
Re: NUMBER column [message #269945 is a reply to message #269943] Tue, 25 September 2007 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Third line in the second link.

Regards
Michel
Re: NUMBER column [message #269962 is a reply to message #269945] Tue, 25 September 2007 04:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
THIRD line?

You cannot expect us to read that much..
Re: NUMBER column [message #269964 is a reply to message #269908] Tue, 25 September 2007 04:40 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Thanks...

May I know how to find the maximum value of NUMBER using the
number of bytes allocated to column with NUMBER datatype
i.e., I want to find the maximum value by doing calculation
using maximum no. of bytes allocated to NUMBER datatype,
in our case 22 bytes.

Can anyone tell me how to do this calculation?

[Updated on: Tue, 25 September 2007 04:40]

Report message to a moderator

Re: NUMBER column [message #269971 is a reply to message #269964] Tue, 25 September 2007 04:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You can use VSIZE to find the amount of bytes used internally.

MHE
Re: NUMBER column [message #269972 is a reply to message #269964] Tue, 25 September 2007 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the question that is NOT answered in the above links?

Regards
Michel
Re: NUMBER column [message #269982 is a reply to message #269908] Tue, 25 September 2007 05:21 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
create table test (id number(20,8));

insert into test values(1233099999.909);

insert into test values(213);

select vsize(id) from test;

 VSIZE(ID)
----------
         8
         3
         
select max(byte_size) from
(
select vsize(id) byte_size from test)         


MAX(BYTE_SIZE)
--------------
     8
Re: NUMBER column [message #269983 is a reply to message #269964] Tue, 25 September 2007 05:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's really easy - you take the number of bytes allocated to the number - in your case 22, and you ut that many 9's in a line.

That's the size of the biggest number you can put in it.

Eg
Number(3) : 999
Number(5) : 99999

Re: NUMBER column [message #269990 is a reply to message #269983] Tue, 25 September 2007 05:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
that is not true for numbers. You mix up length and size in bytes.
The max for a number is 22 bytes, but the max length is 38
Re: NUMBER column [message #270000 is a reply to message #269908] Tue, 25 September 2007 06:32 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi,

Please find the following table,

SQL> desc num
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
A NUMBER

SQL> select a,length(a),vsize(a) from num;
A
--------------------------------------------------
LENGTH(A)
--------------------------------------------------
VSIZE(A)
--------------------------------------------------
124
3
3

12345678901234567890123456789012345678
38
20

1.2345678901234567890123456789012345678120000E+125
40
21

1.0000000000000000000000000000000000000000000E+125
40
2


1. Why the no of bytes used by Row 4 is just 2 bytes but Row 3
uses 21 bytes? Why this difference?
2. It's given that max length as 38,but Row 3 & Row 4 are 40 in length? Why it's so?

I'm confused about this,please clear it..

Thanks...
Re: NUMBER column [message #270003 is a reply to message #270000] Tue, 25 September 2007 06:44 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
gajini wrote on Tue, 25 September 2007 06:32
1. Why the no of bytes used by Row 4 is just 2 bytes but Row 3
uses 21 bytes? Why this difference?
2. It's given that max length as 38,but Row 3 & Row 4 are 40 in length? Why it's so?

I'm confused about this,please clear it..

Thanks...


Did you read maheer reply
Quote:

You can use VSIZE to find the amount of bytes used internally.



Maheer sent a link, please read the document then you will be clear.
Re: NUMBER column [message #270034 is a reply to message #270000] Tue, 25 September 2007 08:41 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Previous Topic: Need help to purge data from a table
Next Topic: problem with sequences
Goto Forum:
  


Current Time: Thu Dec 08 00:17:25 CST 2016

Total time taken to generate the page: 0.09749 seconds