Home » SQL & PL/SQL » SQL & PL/SQL » Storage capacity of NUMBER data-type (10g)
icon7.gif  Storage capacity of NUMBER data-type [message #387231] Wed, 18 February 2009 06:18 Go to next message
J1357
Messages: 33
Registered: November 2008
Member
Gurus,
This has driven me to nuts.
I think there is no upper limit for storing NUMBER datatype.
Correct me if I've missed here ?

Thanks in advance.

J1357
Re: Storage capacity of NUMBER data-type [message #387234 is a reply to message #387231] Wed, 18 February 2009 06:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You're wrong.

SQL Reference
The NUMBER datatype stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10^-130 to (but not including) 1.0 x 10^126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10^126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.

Specify a fixed-point number using the following form:

NUMBER(p,s)

where:
  • p is the precision, or the total number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.
  • s is the scale, or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.
    • Positive scale is the number of significant digits to the right of the decimal point to and including the least significant digit.
    • Negative scale is the number of significant digits to the left of the decimal point, to but not including the least significant digit. For negative scale the least significant digit is on the left side of the decimal point, because the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.


Scale can be greater than precision, most commonly when e notation is used. When scale is greater than precision, the precision specifies the maximum number of significant digits to the right of the decimal point. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point.

It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If a value exceeds the scale, then Oracle rounds it.

Specify an integer using the following form:

NUMBER(p)


This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

Specify a floating-point number using the following form:

NUMBER


The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.
MHE

[Updated on: Wed, 18 February 2009 06:27]

Report message to a moderator

Re: Storage capacity of NUMBER data-type [message #387249 is a reply to message #387234] Wed, 18 February 2009 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is more in SQL Reference, why didn't you post it? Grin

Regards
Michel
Re: Storage capacity of NUMBER data-type [message #387259 is a reply to message #387249] Wed, 18 February 2009 09:33 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel, you have just told me that - according to the Guide - it is not a good idea to post a complete solution. Rolling Eyes

Grin

MHE
Re: Storage capacity of NUMBER data-type [message #387477 is a reply to message #387259] Thu, 19 February 2009 07:01 Go to previous message
J1357
Messages: 33
Registered: November 2008
Member
Thanks Maheer
Previous Topic: clob data type
Next Topic: using nvl in decode function
Goto Forum:
  


Current Time: Sat Dec 10 10:30:42 CST 2016

Total time taken to generate the page: 0.05534 seconds