CODEWITHSUNDEEP
sqloracle-databasenumbers
Anita Geo
Anita Geo

Reputation: 1

Numeric overflow in plsql ORA-01426

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

Upvotes: 0

Views: 2745

Answers (2)

Prosenjit
Prosenjit

Reputation: 3

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

Upvotes: 0

rghome
rghome

Reputation: 8841

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.

Upvotes: 1

Related Questions