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

[Kumar_RP]

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

[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).

[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

[Kumar_RP]

Thanks for your reply Tamil..
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) :).

Thanks in Advance
Regards,
Kumar

[tamilselvan]

Thanks for your reply Tamil..
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) :).

Thanks in Advance
Regards,
Kumar

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

Tamil

[gandolf989]

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

Tamil

For example.


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:

[slimdave]

cheating only slightly ...
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 '933262154439441526816992388562667004907159682643816214685929638952175999932299156089414639761565182 86253697920827223758251185210916864000000000000000000000000';
ELSE
RETURN n * fac(n - 1);
END IF;
END fac;

[gandolf989]

cheating only slightly ...
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 '933262154439441526816992388562667004907159682643816214685929638952175999932299156089414639761565182 86253697920827223758251185210916864000000000000000000000000';
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


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

[slimdave]

Tsh. Fussy fussy. Just calculate it somewhere else and use a lookup table. Be faster ;)

[Kumar_RP]

Hi Folks,
Thanks for your responses about my doubt..

Slimdave... can you please give some more clear idea about whats the need of putting N = 100 as default value.?..

Tamil.. Can you please throw some lights on your answer " Use IBM or FORTRAN "??

Thanks in advance.

Happy Thanks Giving!!!

Regards,
Kumar

[tamilselvan]

Hi Folks,
Thanks for your responses about my doubt..

Slimdave... can you please give some more clear idea about whats the need of putting N = 100 as default value.?..

Tamil.. Can you please throw some lights on your answer " Use IBM or FORTRAN "??

Thanks in advance.

Happy Thanks Giving!!!

Regards,
Kumar

If you can access IBM supercomputer and then use FORTRAN language or C language, to calculate all FAC values, and then use the results in Oracle's function. The function will return varchar2 instead of float.
Oracle RDBMS is not designed for Scientific calculation.

Tamil

[slimdave]

Slimdave... can you please give some more clear idea about whats the need of putting N = 100 as default value.?..

I was cheating by using a precalculated value. Note that I changed the return type of the function to varchar2 also.