-----------------------------------------
•The NUMBER datatype can store numeric values ranges from 1.0 xpower(130,-10) to (but not including) 1.0 x power(10,126).
•NUMBER value requires from 1 to 22 bytes.
•To store a fixed-point number use following form NUMBER (p,s) where p is the precision which specifies the total number of significant decimal digits and it can be 39 or 40. s is the scale which specifies the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.
•The precision p is counted as 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.
•If actual data is 123.67 and you declare as NUMBER(10,1) then it is stored as 123.7
•To store an integer use NUMBER(p) where scale is considered as 0.
•If you use only NUMBER without any precision and scaling value then oracle uses the maximum range and precision of NUMBER.
2)BINARY_FLOAT
----------------------------
•BINARY_FLOAT differ from NUMBER datatype in the way the values are stored internally by oracle database.
•BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype.
•BINARY_FLOAT value requires 5 bytes, including a length byte.
•Maximum positive value for BINARY_FLOAT datatype is 3.40282E+38F and Minimum is 1.17549E-38F.
3)BINARY_DOUBLE
----------------------------------
•BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype.
•Each BINARY_DOUBLE value requires 9 bytes, including a length byte.
•Maximum positive value for BINARY_DOUBLE datatype is 1.79769313486231E+308 and Minimum is 2.22507485850720E-308.
SQL>CREATE TABLE WITH_NUMBER(number_dt NUMBER, num_dt_1 NUMBER(3), num_dt_2 NUMBER(6,7), num_dt_4 NUMBER(3,-2),num_dt_5 NUMBER(4,5), bd_dt BINARY_DOUBLE, bf_dt BINARY_FLOAT);
Table created.
SQL> insert into WITH_NUMBER values(12.7,12.7,12.7,12.7,12.7,12.7,12.7);
insert into WITH_NUMBER values(12.7,12.7,12.7,12.7,12.7,12.7,12.7)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
As end column specification is NUMBER(6,7) and we tried to insert 12.7 so error comes.
SQL> desc WITH_NUMBER
Name Null? Type
----------------------------------------- -------- ----------------------------
NUMBER_DT NUMBER
NUM_DT_1 NUMBER(3)
NUM_DT_2 NUMBER(6,7)
NUM_DT_4 NUMBER(3,-2)
NUM_DT_5 NUMBER(4,5)
BD_DT BINARY_DOUBLE
BF_DT BINARY_FLOAT
SQL> insert into WITH_NUMBER values(12.7,12.7,12.7e-8,121.7,1211e-9,12.7,12.7);
1 row created.
SQL> select * from with_number;
NUMBER_DT NUM_DT_1 NUM_DT_2 NUM_DT_4 NUM_DT_5 BD_DT BF_DT
---------- ---------- ---------- ---------- ---------- ---------- ----------
12.7 13 .0000001 100 0 1.27E+001 1.27E+001
Related Documents
Types of SQL function in Oracle
No comments:
Post a Comment