I have to calculate this
select power((1+(100000/365)),365)-1 from dual
but always gets numeric overflow no matter what i have changed.
i tried with pls_integer, cast, to round the result.
Please help me
-
Your formula must be wrong. I assume 100000 is some kind of interest rate. That equates to 10,000,000%. With daily compound interest, it is going to overflow.rghome– rghome2017-11-30 08:36:45 +00:00Commented Nov 30, 2017 at 8:36
-
A number greater than one raised to the power 365 is an extremely large number. To give some perspective, The number of atoms in the Universe is of the order (10)^80. What are you hoping to accomplish with this calculation of yours?Kaushik Nayak– Kaushik Nayak2017-11-30 08:43:01 +00:00Commented Nov 30, 2017 at 8:43
-
1No, it is divide by 365 to get a daily compound rate. A number slightly larger than zero raised to 365 power is not necessarily very large. It is just that the interest rate is huge.rghome– rghome2017-11-30 09:11:58 +00:00Commented Nov 30, 2017 at 9:11
-
@Anita Geo - try explaining in your question the background around what you are trying to achieve.rghome– rghome2017-11-30 09:20:23 +00:00Commented Nov 30, 2017 at 9:20
-
Thank you all, the formula is right, but the data i have is wrong.Anita Geo– Anita Geo2017-11-30 09:35:33 +00:00Commented Nov 30, 2017 at 9:35
|
Show 1 more comment
2 Answers
The formula is calculating the interest based on a daily compound rate. (I am assuming it is interest - it could equally be population growth or something else).
So the flat rate is 100,000 which equates to 10,000,000%. This is being divided by 365 to get a daily rate (about 27,000%) and then the power function causes compound interest to be applied.
But your initial interest rate is so huge that any compound growth is bound to blow up in a short period of time; 27,000% per day is a lot.
In this case the answer is about 10^890, if my calcuator serves me correctly.
So, in short, your formula is correct. Your parameters are wrong.
Comments
Rounding the returned value will not help as the error is occurring before returning the value to you (which is inside the power function). So there is no way that you can get rid if it until unless you Reduce the operands.
power((1+(100000/365)),365) reaches out to infinity. The variables used in power function cannot handle this bigger value.
The below version shows how it is reaching to infinity. Run it as a script.
SET SERVEROUTPUT ON
WITH FUNCTION to_power(A VARCHAR2,b VARCHAR2) RETURN varchar2 IS
pow VARCHAR2(32767):=1;
BEGIN
dbms_output.put_line('START..');
-----------------------------------------------------------
FOR I IN 1 .. ABS(b)
LOOP
dbms_output.put_line('IN ABS LOOP '||I);
pow:=A*pow;
IF pow='~' THEN
dbms_output.put_line('REACHED INFINITE..');
EXIT;
END IF;
END LOOP;
-----------------------------------------------------------
IF b < 0 THEN
dbms_output.put_line('FOUND NEGATIVE');
IF pow='~' THEN
dbms_output.put_line('WILL NOT DEVIDE 1 BY INFINITE, HENCE RETURNING 0');
RETURN 0;
END IF;
BEGIN
dbms_output.put_line('BEFORE DEVIDE');
RETURN 1/pow;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR OCCURED IN NEGATIVE DEVIDE');
RETURN 0;
END;
dbms_output.put_line('END OF NEGATIVE PROCESSING');
ELSE
dbms_output.put_line('RETURNING + VALUE');
RETURN pow;
END IF;
----------------------------------------------------------
END;
-------------------------------------
SELECT to_power(274,365) FROM dual
