CODEWITHSUNDEEP
oracle-database
Paul Z Wu
Paul Z Wu

Reputation: 575

Store big number in Oracle -- Please give an example that can store 9e125

The Oracle doc says one can store a number up to 9.99...9 x 10125 with up to 38 significant digits: https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i16209.

I tried this:

create table bigtest (t number(38,2));
insert into bigtest values (5e40);

But I got 

[Error] Execution (8: 29): ORA-01438: value larger than specified precision allowed for this column

It is supposed to be able to store 9.99e125, right? Could any one give an example on how to store 9.99e125?

Upvotes: 2

Views: 644

Answers (2)

user5683823
user5683823

Reputation:

One way is to use the number data type without precision and scale specified.

You can specify precision and scale for very large (and also for very small) numbers though. Just keep in mind that negative scale means "that many zeros at the end of an integer" - the total number of digits can be up to precision + absolute value of scale.

In the example below, note that 38 + 84 = 122. The scale must be between -84 and 127, which means that if you do use precision and scale, you can only store numbers < 1e123 - a smaller range than for the full number data type, but still storing very large numbers

create table tbl(x number(38,-84));

insert into tbl values (3.493e121);

select x from tbl;

         X
----------
3.4930E+121

Upvotes: 0

stefan
stefan

Reputation: 2252

See DBfiddle here (Oracle 18c).

create table T1 (
  anumber number
) ;

insert into t1 ( anumber ) values ( 9.99e125 ) ;

select * from t1 ;

ANUMBER
999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Upvotes: 2

Related Questions