Oracle exp function numeric overflow error

Question

I'm trying to do something like this in Oracle:

   select exp(300) from dual

And I get numeric overflow error, how can I make exponential from 300 or bigger number, because I have no idea.

Thanks.

Quick and ugly solution: (cast (exp(150) as BINARY_DOUBLE)) * (cast (exp(150) as BINARY_DOUBLE)); if you have to work with such big numbers may be it's worth to work with logarithms instead of values? — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Feb 16, 2015 at 15:20

Boneist · Accepted Answer · 2015-10-13 09:26:21Z

6

exp(300) is 1.942426e+130, which is greater than the range of Oracle's NUMBER datatype - the biggest number you can have in Oracle is: 9.99...9 x 10¹²⁵ with up to 38 significant digits.

So in short, no, you won't be able to calculate exp(300) in Oracle.

edited Oct 13, 2015 at 9:26

answered Feb 16, 2015 at 15:17

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ruudvan · Accepted Answer · 2015-02-16 15:39:26Z

5

As Boneist said, Oracle NUMBER datatype has its scale from -84 to 127. The EXP function is defined to take in a NUMBER and return a NUMBER. So EXP function can't be directly used.

There are some workarounds though.

Binary_double can store 1.79E308. So for numbers upto E308, you can use that by breaking the exponents into NUMBERs smaller than E127, cast them into BINARY_DOUBLE and multiplying the result.
You could also use an external function in Java/C++/VB etc. to calculate the exponent and return the result as a string. This would assume that you don't have to do any computations on the result, or else you'd need to create an external function that returns the final result after all the necessary computations.

answered Feb 16, 2015 at 15:39

ruudvan

1,3816 silver badges16 bronze badges

1 Comment

Alienware Over a year ago

Thanks all, I will try the first step of ruudvan2002 answer.

Collectives™ on Stack Overflow

Oracle exp function numeric overflow error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related