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