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???

[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 '93326215443944152681699238856266700490715968264381621468592963895217599993229915608941463976156518286253697920827223758251185210916864000000000000000000000000';
  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 '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.

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