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

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

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

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

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

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:

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;

---

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

---

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

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