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

Printable View

11-21-2005, 01: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, 02: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, 03: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, 03:46 PM
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
11-21-2005, 04:23 PM
tamilselvan

Quote:

Originally Posted by 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

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

Tamil
11-21-2005, 05: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, 05: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, 05: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, 06:02 PM
slimdave

Tsh. Fussy fussy. Just calculate it somewhere else and use a lookup table. Be faster ;)
11-23-2005, 01:01 PM
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
11-23-2005, 01:35 PM
tamilselvan

Quote:

Originally Posted by 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

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
11-23-2005, 02:21 PM
slimdave

Quote:

Originally Posted by Kumar_RP

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.