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

1. Junior Member
Join Date
May 2001
Location
Dallas, US
Posts
78

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

2. Member
Join Date
Nov 2001
Location
UK
Posts
152
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).

3. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135

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

4. Junior Member
Join Date
May 2001
Location
Dallas, US
Posts
78
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) .

Regards,
Kumar

5. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
Originally Posted by Kumar_RP
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) .

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

Tamil

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

7. Foreign Script Kiddie
Join Date
Aug 2002
Location
Posts
5,253
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;```

8. 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```

9. Foreign Script Kiddie
Join Date
Aug 2002
Location
Posts
5,253
Tsh. Fussy fussy. Just calculate it somewhere else and use a lookup table. Be faster

10. Junior Member
Join Date
May 2001
Location
Dallas, US
Posts
78
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 "??

Happy Thanks Giving!!!

Regards,
Kumar

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Click Here to Expand Forum to Full Width

×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.