# How to get ride of ORA-01426: numeric overflow error?

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• 11-21-2005, 12:45 PM
Kumar_RP
How to get ride of ORA-01426: numeric overflow error?
Hi,
I am tring to create this function for factorial calculation.

CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
BEGIN
IF n = 0 THEN
RETURN 1;
ELSIF n = 1 THEN
RETURN 1;
ELSE
RETURN n * fac(n - 1);
END IF;
END fac;

When i am executing this function as

select FAC(34) from dual
*
ERROR at line 1:
ORA-01426: numeric overflow

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large

Can someone help me about the problem with this function.

Thanks
Regards,
Kumar
• 11-21-2005, 01:11 PM
Scorby
You've probably gone beyond the limit Oracle can cope with for a number variable. I think it can only go up to number(39).
• 11-21-2005, 02:14 PM
tamilselvan

CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER

Change the NUMBER to FLOAT.

CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN FLOAT

SQL> select fac(34) from dual ;

FAC(34)
----------
2.9523E+38

Tamil
• 11-21-2005, 02:46 PM
Kumar_RP
But i have one more question for you :).

select FAC(83) from dual;
FAC(83)
----------
3.946E+124

select FAC(84) from dual;
FAC(84)
---------
~

Is there any other work around to resolve this.. I need to calculate till FAC(100) :).

Regards,
Kumar
• 11-21-2005, 03:23 PM
tamilselvan
Quote:

Originally Posted by Kumar_RP
But i have one more question for you :).

select FAC(83) from dual;
FAC(83)
----------
3.946E+124

select FAC(84) from dual;
FAC(84)
---------
~

Is there any other work around to resolve this.. I need to calculate till FAC(100) :).

Regards,
Kumar

You need to use IBM Mainframe and FORTRAN to calculate FAC(100).

Tamil
• 11-21-2005, 04:08 PM
gandolf989
Quote:

Originally Posted by tamilselvan
You need to use IBM Mainframe and FORTRAN to calculate FAC(100).

Tamil

For example.

Code:

```CREATE OR REPLACE FUNCTION fac ( n NUMBER )   RETURN NUMBER IS BEGIN   IF n = 0   THEN       RETURN 1;   ELSIF n = 1   THEN       RETURN 1;   ELSE       RETURN n * fac ( n - 1 );   END IF; END fac; / SELECT TO_CHAR(fac( 9), '999,999,999') factor_9,       TO_CHAR(fac(10), '999,999,999') factor_10,       TO_CHAR(fac(10) - fac(9),                                   '999,999,999') factor_diff,       TO_CHAR(fac(30), '999,999,999,999,999,999,999,999,999,999,999') factor_30   FROM dual; FACTOR_9    FACTOR_10    FACTOR_DIFF  FACTOR_30 ------------ ------------ ------------ --------------------------------------------     362,880    3,628,800    3,265,920  265,252,859,812,191,058,636,308,480,000,000```
You can see that the result grows exponentially. Is this a homework assignment??? :rolleyes:
• 11-21-2005, 04:32 PM
slimdave
cheating only slightly ...
Code:

```CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN varchar2 IS BEGIN   IF n = 0 THEN      RETURN 1;   ELSIF n = 1 THEN     RETURN 1;   ELSIF n = 100 THEN     RETURN '93326215443944152681699238856266700490715968264381621468592963895217599993229915608941463976156518286253697920827223758251185210916864000000000000000000000000';   ELSE     RETURN n * fac(n - 1);   END IF; END fac;```
• 11-21-2005, 04:39 PM
gandolf989
Quote:

Originally Posted by slimdave
cheating only slightly ...
Code:

```CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN varchar2 IS BEGIN   IF n = 0 THEN      RETURN 1;   ELSIF n = 1 THEN     RETURN 1;   ELSIF n = 100 THEN     RETURN '93326215443944152681699238856266700490715968264381621468592963895217599993229915608941463976156518286253697920827223758251185210916864000000000000000000000000';   ELSE     RETURN n * fac(n - 1);   END IF; END fac;```

Perhaps you can create a case function to deal with all of the numbers between 30 and 100. :D

Code:

```CCDOTTSVR@vizdev> select fac(99) from dual; select fac(99) from dual       * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10 ORA-06512: at "CCDOTTSVR.FAC", line 10```
• 11-21-2005, 05:02 PM
slimdave
Tsh. Fussy fussy. Just calculate it somewhere else and use a lookup table. Be faster ;)
• 11-23-2005, 12:01 PM
Kumar_RP
Hi Folks,