Home » SQL & PL/SQL » SQL & PL/SQL » number datatype
number datatype [message #266804] Tue, 11 September 2007 12:23 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

I have a doubt regarding to the NUMBER DATATYPE. I have read in the Oracle Student guide SQL book that in a NUMBER DATATYPE , the Precision values accepts from 1 to 30 and the Scale value it accepts from -84 to 127.

Can somebody help out in that last line " A scale value accepts from -84 to 127" .

Number(P,S) , where P ranges from 1 to 30
and S ranges from -84 to 127.


Looking forward for the reply sir.

Regards,

Raj
Re: number datatype [message #266805 is a reply to message #266804] Tue, 11 September 2007 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NUMBER Datatype

Regards
Michel
Re: number datatype [message #266809 is a reply to message #266805] Tue, 11 September 2007 12:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Whoops. Documentation bug ??

Table 2-2 Storage of Scale and Precision

         Actual Data 	Specified As 	Stored As
....
Line 2 : 123.89          NUMBER(3)       124
....
Line 5 : 123.89          NUMBER(3)       exceeds precision
....


./fa/1600/0/

Line 2 should be the correct one for the NUMBER(3), though. Wink

Re: number datatype [message #266812 is a reply to message #266805] Tue, 11 September 2007 12:53 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Sir,

I did read the link what you have sent me, but I could'nt understand what it meant .

Like if my number is number(5,-6) , what is the value of this and how is that calculated.

plz clarify my doubt.

Regards,

Raj
Re: number datatype [message #266819 is a reply to message #266812] Tue, 11 September 2007 13:08 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (val1 number, val2 number(5,-6));

Table created.

SQL> insert into t select val, val from (select 1.111111*level*power(10,level+4) val from dual connect by level <= 6);

6 rows created.

SQL> select * from t;
                VAL1                 VAL2
-------------------- --------------------
            111111.1                    0
             2222222              2000000
            33333330             33000000
           444444400            444000000
          5555555000           5556000000
         66666660000          66667000000

6 rows selected.

SQL> insert into t select val, val from (select 1.111111*7*power(10,7+4) val from dual );
insert into t select val, val from (select 1.111111*7*power(10,7+4) val from dual )
                          *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

It means the 6 first digits at left of point are ignored and then you can have 5 digits (so less than power(10,11)).

Regards
Michel
Previous Topic: May be Cartesian Join
Next Topic: Need help with understanding sql format
Goto Forum:
  


Current Time: Fri Dec 09 23:38:09 CST 2016

Total time taken to generate the page: 0.29364 seconds