
How to get ride of ORA01426: 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:
ORA01426: numeric overflow
ERROR at line 1:
ORA06502: PL/SQL: numeric or value error: number precision too large
Can someone help me about the problem with this function.
Thanks
Regards,
Kumar
RP Kumar
You Can Win, if u believe Yourself

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
RP Kumar
You Can Win, if u believe Yourself

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

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???
this space intentionally left blank

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;

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.
Code:
CCDOTTSVR@vizdev> select fac(99) from dual;
select fac(99) from dual
*
ERROR at line 1:
ORA06502: PL/SQL: numeric or value error: character to number conversion error
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
ORA06512: at "CCDOTTSVR.FAC", line 10
this space intentionally left blank

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
RP Kumar
You Can Win, if u believe Yourself
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
